SUM specific rows based on multiple selections in a chiclet slicer.

[CSilva]

I am using a chiclet slicer to filter a data set based on the client grouping code selected. Before being filtered, the data set looks something like this

ACCT_NUM	Lab_Prod	OH_Prod	AMOUNT
16134	TM123	CSOH	56.00
196	CSOH		899.00
12354	B3	CSOH	100.00

Now, there is a section of my desktop which is the sum of all the rows which have a grouping code in the Lab_Prod column that matches the grouping code selected in the chiclet slicer (all these grouping codes will end in OH). Because of the way the data is set up, when you select a client grouping code with the chiclet slicer, the data then looks like this: 

ACCT_NUM	Lab_Prod	OH_Prod	AMOUNT
16134	TM123	CSOH	56.00
12354	B3	CSOH	100.00

Since the data set is setup in such way so that the group code (CSOH in the example above) does not appear simultaneously in the Lab_Prod column and OH_Prod  column for any given row, all the rows with the group code in the OH_Prod column remain (and only those rows) while all the rows with the group code in the Lab_Prod are filtered out. 

Once again, my goal is to sum all the rows with a Lab_Prod grouping code that match the one selected in the chiclet slicer.
To get the desired sum, I used the formula: 

OH_Total = 
IF(
ISFILTERED('Grouping Code'[OH_Prod]), 
CALCULATE(
SUM(LaborData[AMOUNT]),ALL('Grouping Code'[OH_Prod]), ALLSELECTED('Grouping Code'[OH_Prod])=LaborData[Lab_Prod])
)

And with this formula, things work just fine; However, an issue arises whenever 2 or more  options are selected in the chiclet slicer. When that is done, I receieve an error or a 0.00. My assumption is that it is attempting to match multiple options in the chiclet slicer with multiple values in the Lab_Prod column, and cannot do that. 

So, my question is: can I adjust my formula in such a way as to allow multiple client grouping codes to be selected in the slicer and still be able to sum up the proper rows? 

[v-kkf-msft · Accepted solution]

Hi @CSilva ,

Try the following formula:

Measure = 
IF(
    MAX(LaborData[Lab_Prod]) in ALLSELECTED('Grouping Code'[OH_Prod]),
    CALCULATE(
        SUM(LaborData[AMOUNT]),
        ALLSELECTED('LaborData')
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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