Excel 2013 PowerPivot DAX - Getting the correct value with criterias on different tables

[dluhut]

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.

[Anonymous]

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) ?

[dluhut]

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'

[Anonymous]

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.

[dluhut]

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.

[Anonymous]

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"

[dluhut]

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.

[dluhut]

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?