Sum at different granularity than slicer

[AveragePotato]

Requirements: 

- show sales for each Employee

- show sales for each StoreID

- show sales for each State

Slicers:

- only two slicers allowed on this report

- one for EmployeeID

- one for StoreID

EmployeeID	StoreID	State	SalesAmount
1	5	New York	$10
2	5	New York	$12
3	7	New York	$15
10	13	New York	$5

So the report needs to show sales for the Employee, Store, and State. 

Let's say the slicer selections are EmployeeID is 2 and StoreID is 5... We would expect to see

Employee sales at $12

Store sales at $10 + $12 = $22

State sales at $10 + $12 + $15 + $5 = $42

So the problem is that I can't seem to figure out how to sum for the entire State when an Employee is selected in that state. Power BI sums for just those rows that EmployeeID pertains to. This isn't a problem for the StoreID, because there is a slicer for that. I am able to sum for Employee sales and Store sales and get my desired result above. The field that's wrong is the State sales. It would show $22 - same as the Store sales.

I need help on summing for the entire State sales regardless of which Employee/Store is selected in that State. I was able to get my desired result for State sales by adding a State slicer and using REMOVEFILTERS ( 'EmployeeID' ) and REMOVEFILTERS ( 'StoreID' ) in the measure for State sales. But unfortunately, this is not a viable solution because requirements do not permit adding another slicer.

I have been struggling with this for quite some time and cannot seem to figure out a solution. I'm fairly new to PowerBI. I come from a Cognos background and in Cognos, we could simply do total(sales for state) and that worked, but I'm having trouble with this in Power BI.

Thanks so much!

[v-yangliu-msft · Accepted solution]

Hi  @AveragePotato ,

Here are the steps you can follow：

1. Create measure.

sales for each Employee =
SUMX(
    ALLSELECTED('Table'),[SalesAmount])

sales for each StoreID =
var _selectemployeeID=SELECTEDVALUE('Table'[EmployeeID])
var _selectStoreID=SELECTEDVALUE('Table'[StoreID])
return
SUMX(
    FILTER(ALL('Table'),
    'Table'[StoreID]=_selectStoreID),[SalesAmount])

sales for each State =
var _selectemployeeID=SELECTEDVALUE('Table'[EmployeeID])
var _selectStoreID=SELECTEDVALUE('Table'[StoreID])
return
SUMX(
    FILTER(ALL('Table'),
    'Table'[State]=    MAXX(FILTER(ALL('Table'),'Table'[EmployeeID]=_selectemployeeID&&'Table'[StoreID]=_selectStoreID),[State])),[SalesAmount])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly