CALCULATE DOES NOT FILTER AS INTENDED

[George87]

Good day all, I am new to PB and I am having problems in obtaining a value that is the difference between demand and capacity for a given region.

The goal is to create a measure that gives the difference value between demand and capacity for a given region (in the example APAC), regardless of external filters (applied in region slicer). I have used CALCULATE, but it only works for me when in the external slicer I mark the APAC region, otherwise it looks blank.

If I am right, CALCULATE is supposed to remove the pre-existing filters and apply the filter you set to, but in my case, it doesn't work.

I hope you can help me to find the error I am making. Thank you in advance

[ValtteriN]

Hi, 

You need to add function such as ALL or REMOVEFILTERS: 
ALL function (DAX) - DAX | Microsoft Learn
CALCULATE function (DAX) - DAX | Microsoft Learn
REMOVEFILTERS function (DAX) - DAX | Microsoft Learn

In addition to this I recommend having separate measures which you would refer in this measure. E.g. 
[Sales all] = CALCULATE(SUM('Master Capacity'[weekly capacity]), ALL('Master Capacity'))

[Sales selected] =SUM('Master Capacity'[weekly capacity])

[Sales apac] = CALCULATE(SUM('Master Capacity'[weekly capacity]), ALL('Master Capacity'),'Location'[[location region] (groups)]="APAC")

[Sales diff] = [Sales selected] - [Sales apac]

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[George87]

Hi ValtteriN, Thank you for your answer. But if I use ALL, is it not removing all the externals filters applied? I would like to remove only the external filter "Location" (Location slicer) and then set in the formula the region I want, but keep the other external filters applied. Do you think is any way to do it?

[ValtteriN · Accepted solution]

Hi,

You can use ALL to remove only the location: ALL( [<table> | <column>[, <column>[, <column>[,…]]]] )

[Sales all locations] = CALCULATE(SUM('Master Capacity'[weekly capacity]), ALL('Master Capacity'[location]))