Forum Discussion
Excel 2013 PowerPivot DAX - Getting the correct value with criterias on different tables
Hello,
I have the following tables.
I like to accomplish similar to this post (Sum of values based on distinct values in other column), where given the slicerTable[Province], or slicerTable[City] that end user click, it'll know to count ONLY once for the 'BE' amount, which is denoted by Profit Center that ends with 0000 (only 4 zeros).
I tried to get the first 'Measure', where from the given post, was written as:
Measure = MAXX(DISTINCT(Table1[Country]),MAX(Table1[Weight])).
I tried to modified it but didn't get the correct answer:
Measure:=MAXX(DISTINCT(lookupTable[Province]),CALCULATE(MAX(factTable[Amount]),RIGHT(lookupTable[Profit Center],4)="0000")).
The logic that I had here (which obviously isn't working to what I had) is that, get the distinct 'values' on lookupTable[Province], and for each of that 'values', get the max 'values' where the profit center ended with "0000".
What I'd like to accomplish here is that, when end user select an item(s) in either the 'Province' or 'City' that's from the slicerTable, I'd like to have the correct calculation
Example 1: User selects ONLY 1 value from the [City] (Ottawa). Thus in this case, I'd like powerpivot table to only get the value for [Profit Center] 10010000 and 10010200 ==> 20+19 = 39 (for [Province] Ontario Assume if I don't have [City] and/or [Profit Center] in the column fields).
Example 2: User selects 2 OR MORE values from the [City] (Ottawa, Kingston and Richmond). Thus in this case, I'd like powerpivot table to only get the value for 'Profit Center' 10010000, 10010200 and 10010300 for Ontario and 20010000 and 20010200 for British Columbia ==> 20 + 19 + 11 = 50 (for Ontario) && 18 + 18 = 36 (for British Columbia).
Example 3: Assume if User mistakenly selects 'Vancouver', 'Richmond' and 'Coquitlam' and 'Victoria' from the [City] slicers, given the above examples explanation, I'd like powerpivot table to only get the value for [Profit Center] 20010000, 20010100, 20010200 20010300 and 20010400 which is the same as clicking 'British Columbia' on the [Province] slicer. ==> 18 + 15 + 18 + 11 + 19 = 81 (for British Columbia)
In short, the BE or [Profit Center] (ended with "0000" - 4 zeros) is only counted once for each [Province] given their [City] selections.
Below is another screenshot that I'd like to accomplish via Excel 2013 PowerPivot.
7 Replies
Two questions to on your data model:
1) What is the purpose of the slicer table? (As opposed to just using the lookupTable directly)
2) You seem to be implying the ProfitCenter has some special meaning in the first 4 vs last 4 digits -- I'm wondering if you should explicitly split those into 2 more columns (1001 and 0000 for Ontario, BE) ?
Hi Scottsen,
Thanks for your reply.
Below is the asnwer.
1) What is the purpose of the slicer table? (As opposed to just using the lookupTable directly)
Ans: The purpose of this is so as to avoid user from choosing the 'BE' values on the city, if I were to use the lookupTable directly.
2) You seem to be implying the ProfitCenter has some special meaning in the first 4 vs last 4 digits -- I'm wondering if you should explicitly split those into 2 more columns (1001 and 0000 for Ontario, BE) ?
Ans: The ProfitCenter does have some meaning, as it acts as a unique identifier for the city. the last 4 digits is the only one that determines if it's a 'BE' or not. The first 4 digits determines the province. But if looking at the last 4 digits, we won't be able to determine the city, EXCEPT the 'BE' as it'll always be '0000'.
So for example, 10010100 is Toronto and 20010100 is Vancouver. However, if without the first 4 digits, and just by looking at the last 4 digits, I won't be able to distinguish if it's 'Toronto' or 'Vancouver'
Not sure if your slicerTable has a relationship with your lookupTable? Also not sure if you have the same City name for multiple Province -- and how that impacts things?
I almost want to think something like this would work:
Total Amount := SUM(factTable[Amount])
Total Non-BE Amount := CALCULATE([Total Amount], lookupTable[City] <> "BE")
Total BE Amount := CALCULATE([Total Amount], lookupTable[City] = "BE", VALUES(lookupTable[Province]))
Super Total Amount := [Total Non-BE Amount] + [Total BE Amount]
The [Total BE Amount] is going to add/replace filters on the city column to be just BE... and the VALUES( ) portion will make sure you stay in the same Province(s) ?
It's a theory! We can iterate.
