Filtering Data Properly

[srdirect]

Hi

I have 3 tables of data that each have a similar structure.
Example [North South Central ]

within each table we built a measure to add up the sales for that particular measures are similar while
Ex SumSalesNorth, SumSalesSouth, SumSalesCentral.

another measure was built which adds all the sales together with the intention of ONLY using that measure throughout the entire pivot table structure
[Ex SumSales= SumSalesNorth + SumSalesSouth + SumSalesCentral]


The issue is that when you use the measure "SumSales" against the products field of a particular table it will sum up the sales from THE OTHER TABLES (and add the value to the SUM)

Ex using the products as column from North and use SumSales as the measure add the data
I should get:



....... What I get .... what I want
Coats .......300 ...... 100
Scarfs...... 225 ...... 25
Gloves...... 210 ...... 10

as you see an extra 200 is being pulled in from the other 2 tables.


how do I filter such that the measure only adds data within that table (when it is being referenced) and does not add data when being pulled from external source

Thanks!!

 

[scottsen]

Can you paste the measure(s) that is causing the problems? I suspect an ALL() not doing what you expect?

 

[srdirect]

I am not using all, (will be comparing different fields in each of the tables, just trying to roll it together)

(their are 3 of these just posting 1
sum south:=CALCULATE(SUM(south[SalesinDollars]))



Total sum:=South[Sum south]+North[sum north]+Central [sum Central]

 

[scottsen]

Okay, re-reading you have a relationship/filter issue here. What is "on rows" of your pivot table? That needs to come from a "common" lookup table if possible.

 

[srdirect]

yes it is a common lookup table where the different product types are listed. [I should mention that each product is exclusive to a region.



columns are dates


rows are product types.


Interesting is that if I put region type in the 'report filter' box/dialog (in powerpivot field list) I am able to get the correct calculation

 

[srdirect]

I should clarify (slightly)

when I select the product type from a particular region's table (aka I am selecting within one of the regions table in this example lets use south ) and use the global sum (here SumSales)
I am getting the error, if I include the refence lookup Table than I wont generate the error

 

[scottsen]

Your model looks like this? The "circle" on each relationship is on the fact/data table, and the arrow is on the lookup table?

 

[srdirect]

Correct
1.[Products are unique to each region]
2. I also have a date key (another table but not important for this example)

 

[scottsen]

Well, the good news is you seem to be doing everything correctly As long as your product types on rows/columns are from that upper lookup table... you should be good.

You can not (of course) expect any filters to flow from North to South (etc), they must come down from the lookup table.

If you think something is still weird, can you share out your workbook? (google drive, one drive, dropbox)