Forum Discussion
CALCULATE DOES NOT FILTER AS INTENDED
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
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]))
3 Replies
- ValtteriN
Community Champion
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/- 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
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]))
