PowerPivotTable Slicers but taking an additional "base" value

[dluhut]

Hello BI Experts!

Below is the table that I had.

In a powerpivot table, I had the [Province] follow by [City] and [Profit Center] across the column fields. The [City] and [Profit Center] fields are just a check for me to see if the number agrregated correctly.

Now I have 2 slicers for user end, which are [Province] and [City], where if user selects the item(s) on [Province], it'll give the values for the [Province] that user's had selected. The same goes with [City].

My question is, IF a user SELECT an item(s) that's of a particular city, but not all of the values in the province, is it possible to have powerpivot table to add to the 'base', where in the table below, base is denoted to be (blank) and/or Profit Center that ends with 0000 WITH the item(s) that was selected?

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 ==> 10+20 = 30 (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 ==> 10 + 20 + 25 = 55 (for Ontario)  &&  55 + 10 = 65 (for British Columbia).

Example 3: Assume if User mistakenly selects 'Vancouver', 'Richmond' and 'Coquitlam' 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 and 20010300 which is the same as clicking 'British Columbia' on the [Province] slicer. ==> 55 + 5 + 10 + 20 = 90 (for British Columbia)

In short, the 'base' or [Profit Center] is only counted once for each [Province] given their [City] selections.

Province	City	Profit Center	Amount
Ontario		10010000	10
Ontario	Toronto	10010100	15
Ontario	Ottawa	10010200	20
Ontario	Kingston	10010300	25
Ontario	Scarborough	10010400	30
British Columbia		20010000	55
British Columbia	Vancouver	20010100	5
British Columbia	Richmond	20010200	10
British Columbia	Coquitlam	20010300	20

[v-caliao-msft · Accepted solution]

@dluhut,

Please refer to the steps below to achieve your requirement.

1. Create a slicer table like below.
   
2. Create two measures in your original table.
   SelectedCity = CONCATENATEX(slicercity,CALCULATE(MAX(slicercity[City])),"")
   SelectedProvince = CONCATENATEX(slicercity,CALCULATE(MAX(slicercity[Province])),"")
3. Create another measure in your original table.
   Measure = IF(AND(IF(ISERROR(SEARCH(MAX(Sales[City]),Sales[SelectedCity],1)),0,1),IF(ISERROR(SEARCH(MAX(Sales[Province]),Sales[SelectedProvince],1)),0,1)),1,0)
4. Add this measure to your visual filter like below.
   

Results

Regards,

Charlie Liao

[dluhut]

Thanks for your reply.

Tried to do the same exact steps, and realized that in PowerPivot, I don't have CONCATENATEX function.

What would be the alternative?

PS: My company doesn't have PowerBI, thus I'm doing this on PowerPivot MS Excel 2013