Using FILTER and IN to create measure to calculate TOTAL not working in Power BI Desktop

[angela_marie]

I need to calculate a % of total, the issue is I need to exclude one field.  

Total SLA = CALCULATE(COUNTX('SL_Data',[SLA GOAL]),ALL('SL_Data'[Skill Name]))

This measure works for the TOTAL, however I need to add this:

,FILTER('SL_Data','SL_Data'[Skill Name] IN {"Clinical","Equipment","Mechanical Room"})) or

,FILTER('SL_Data','SL_Data'[Skill Name] <> "Direct Queue"

The issue when I do the TOTAL amount no longer works and my % of total is now 100%.

How can I create a TOTAL measure where I exclude a value?  

[v-xinruzhu-msft · Accepted solution]

Hi @angela_marie 

Based on your description, you need to modify your measure to the following

Total SLA =
CALCULATE (
    COUNTX ( 'SL_Data', [SLA GOAL] ),
    ALLSELECTED ( 'SL_Data'[Skill Name] )
)

Then create a new meaure

MEASURE =
CALCULATE (
    [Total SLA],
    'SL_Data'[Skill Name] IN { "Clinical", "Equipment", "Mechanical Room" }
)

It can work.

Best Regards!

Yolo Zhu

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

[amitchandak]

@angela_marie , if you need a total of those three , then you need use

FILTER(all('SL_Data') ,'SL_Data'[Skill Name] IN {"Clinical","Equipment","Mechanical Room"}))

Power BI How ALL Works: different ways to use- https://youtu.be/LXgzlZ38mwU