Forum Discussion
Excel 2013 PowerPivot DAX - Getting the correct value with criterias on different tables
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.
Thanks for the reply scottsen,
Tried your method and didn't get the correct result.
Currently I don't have any relationship, as I don't know how I'm going to create one where I have both the lookupTable and the slicerTable.
The formula(s) below that you gave me, I've indicated what are the values that it returned me, when I had the 'Province' and 'City' that's on my slicerTable put in the 'Columns' of the Pivot Table.
Total Amount := SUM(factTable[Amount])
Result: 149 throughout
Total Non-BE Amount := CALCULATE([Total Amount], lookupTable[City] <> "BE")
Result: 149 throughout
Total BE Amount := CALCULATE([Total Amount], lookupTable[City] = "BE", VALUES(lookupTable[Province]))
Result: 149 throughout
Super Total Amount := [Total Non-BE Amount] + [Total BE Amount]
Result: 298 throughout
PS: I'm flexible in terms of changing the lookupTable and/or slicerTable, as long as the slicer for 'City' does not have the 'BE' values in. So if there's an easier way to get it done and have the correct value, i'm fine changing it.
I don't know for sure that relating your lookup table and slicer table is the "best" way to go -- but it should certainly be *a* possible solution. Easiest is probably just to include Profit Center in your slicer table and relate via that column... making real sure the relationship is flowing the current direction (slicer table is the 1 side, lookup table is the many side... though, maybe just use 1:1).
It is also possible to do without the relationship, but we will need to adjust the measures to basically create a "virtual relationship"
Tried to do what you asked, which is having the [Profit Center] at the slicerTable and then create a relationship with the lookupTable, along with the previous' post formula(s) that you've wrote and still didn't get the correct result.
Anyone has a solution for this?
Been playing around for weeks and still not solution.
Perhaps, there's no solution at all with what I'm trying to accomplish?