PowerPivot relations

Question

I'm trying to build a Pivot table out of 4 tables that i have loaded in PowerPivot. the information in the rows of the Pivot is like this: Region account Products

One of the tables has the forecasted values for all 4 quarters for each Region but when i add it as Sum Values it repeats the entire number for each product but i just want the total. Is there a way to fix it without having to change the color of the font to white to hide it?

Answer 1

it would be best for these to be actual calculated fields rather than implicit calculated fields (i.e. dropping the column onto the pivot table). This way you can utilize the isfiltered function to drive what you are looking for.

i.e. if your data is at this granularity:

Region Customer Product Ext_Sell_Price Q1 Q2 Q3 Brazil Acuntia 39XX 17246 743799 672599 748339 Brazil Acuntia 51XX 130918 743799 672599 748339

or you have the sales table and a region forecast table with a relationship to the sales table, you could check to ensure customer/product were not filtered in order to show the forecast:

Q1 Total:=IF( ISFILTERED( Table1[Customer]) || ISFILTERED( Table1[Product]), BLANK(), MIN( Table1[Q1]) )

Answer 2

Without more info can't be sure, but this looks like you have not set up the model to support the Quarterly Forecast Measures in PowerPivot. To get this to work, there needs to be one-to-many relationships between identifying fields in each table.

PowerDax answered based on the assumption that you intended for the Quarterly Forecast to roll up to the region because you phrased you're question to indicate the forecast data is at the regional granularity.

If the forecast data is in one fact table, and the EXT_SELL_PRICE is in another, you will need to set up dimension tables each containing unique lists of the members of each field you are using to slice and dice the report (looks like End User Region, Child Customer, forecast product name). Relate all you're fact tables (tables that contain you're actual data) to these and put the dimension table fields in the Rows area of the pivot table. Then the measures will have some way of understanding how they relate to the rows they are being applied to.

Hope that helps