I am trying to calculate "Distinct Sum" in DAX Powerpivot. I already have found help here: http://stackoverflow.com/questions/22613333/dynamic-sum-in-dax-picking-distinct-values
And my query is similar but extended further. I am seeking to find solution for such distinct Sum, but via two additional dimension (Month + Country)
In data example below there is Revenue performance on Part Number granularity. in Data there is also Shop Dimension, however Revenue is repeating on shops, is duplicated.
In the post mentioned above there is following solution:
Support:=MAX(Table1[Revenue])
DistinctSumOfRev:=SUMX(DISTINCT(Table1[Part_Num]),[Support])
It is work perfectly if you use Filter/Column/Row: Country and Month. But if aggregate for All countries, or show performance on whole quarter, then solution will set MAX Revenue thru all countries/Months for and Part Number, which is not correct.
How to include into above solution also those two additional dimensions. Basically to tell DAX that unique combination is PartNum+Country+Month
Country Month Part_Num Shop Revenue
----------------------------------------
UK 1 ABCD X 1000
France 1 ABCD X 500
France 1 ABCD Y 500
UK 2 ABCD X 1500
UK 2 ABCD Y 1500
UK 1 FGHJ X 3000
France 1 FGHJ X 600
UK 2 FGHJ X 2000
