subtraction of two measures

[KamilRetkiewicz]

Hi everyone

I have such a situation, on the report I have two measures visualized as cards. One measure concerns the period of last year (I turned off the slicer with data for this visualization - the value must be constant), the slicer must have an influence on the second measure and it must be interactive. In the third card I need to visualize the result of the measure that subtracts both of these measures. Removing the influence of filters in the measure does not work using ALL or REMOVEFILTERS, in each case the result from the first measure takes the value blank. Has anyone encountered such a problem? Thank you in advance for your answers

Thank you in advance for your answers. 

[v-zhouwen-msft · Accepted solution]

Hi pbiuseruk ,thanks for the quick reply, I'll add more.

Hi @KamilRetkiewicz ,

Regarding your question, are your slicer fields from your date table?

What you are removing here is the filtering effect of the fact table.

Something like this:

Best Regards,
Wenbin Zhou

[pbiuseruk]

Hi,

I think I get what you mean and this is how I'd probably do it.

I'd make a third measure and this measure would include both of the measures logic within it and have a result which would be the subtraction.

Step 1: Make variable with the logic from the initial measure - use a calculate statement and filters to "lock in" the filter context.

Step 2: Make another variable with the logic from the second measure and also use a calculate statement but either don't use a calculate statement (so that the slicers filter is applied)

Step 3: Use the RESULT command and then do variable 2 minus variable 1

[KamilRetkiewicz]

Hi, I tried this option and unfortunately the slicer was still higher in the hierarchy than the filters in the measure. It correctly took the value from the second measure, but treated the first as blank 😞

[pbiuseruk]

Hi,

Could you show an example of the first measure because a calculate functions main use is to override filter context on a page. The slicer won't be higher than a filter within the calculate function - if for some reason you get blanks though (which I have aswell in the past), you can try to do something like this:
Calculate(Logic,Filter(All(<Table>), Filter Expression)) - This isn't recommended because it's much more resource intensive on the Power BI engine but it's the only thing that works in some situations.

[KamilRetkiewicz]

TradePayablesReclass =
VAR TradePayables2023Value =
CALCULATE(
SUM(CF_WC[YTD]),
REMOVEFILTERS(CF_WC[PERIOD]),
CF_WC[ACCOUNT] = "BSM_CL_LIAB_TR_3P",
CF_WC[PERIOD] = "202312", 
CF_WC[SV] = "ACTUAL_LAST"
)
VAR TradePayablesMonthValue =
IF(
HASONEVALUE(CF_WC[PERIOD]), 
CALCULATE(
SUM(CF_WC[YTD]),
CF_WC[ACCOUNT] = "BSM_CL_LIAB_TR_3P"
),
BLANK()
)
RETURN
TradePayablesMonthValue - TradePayables2023Value

And this is the third measure with VAR's.

[KamilRetkiewicz]

Yes ofc ; 

this is first measure: 

TradePayables2023 = CALCULATE(

SUM(CF_WC[YTD]),

    CF_WC[ACCOUNT] = "BSM_CL_LIAB_TR_3P",

    CF_WC[PERIOD] = "202312",

    CF_WC[SV] = "ACTUAL_LAST") - > for this measure i turned off slicer's impact.

And here my previous one where i turned off it by measure:
TradePayables2023_fixed =
CALCULATE(
SUM(CF_WC[YTD]),
REMOVEFILTERS(CF_WC[PERIOD]), 
CF_WC[ACCOUNT] = "BSM_CL_LIAB_TR_3P",
CF_WC[PERIOD] = "202312", 
CF_WC[SV] = "ACTUAL_LAST"
)

The second measure calculates the value for the months selected on the slicer.

The third measure must be the result of subtracting these two

[pbiuseruk]

Interesting one - the REMOVEFILTERS wouldn't actually do anything in the second measure you made because you're specifiying a value for that column in the next part. I really can't see why it's not working for you though - if possible, could you provide some dummy data and share the pbix? - just the page with the issue.

[KamilRetkiewicz]

I don't know if it has any significance, but the months from the CF_WC table are mapped to the Calendar, but disabling the slicer's influence should not really matter in this aspect