Reference filtered values in a formula

[Anonymous]

A slightly tricky one to explain, just wondering if anyone can help - or at least confirm it's not possible in Power BI.

We have 2 dimensions (Base Category & Target Category)  &  2 measures (NTO_Amount_Base & NTO_Amount_Target)

In my ropey screenshot below, the Base category slicer filters the Base Amount measure & the Target Slicer filters the Target Amount.

This works as expected.

What I need to do is create a new measure that simply calculates Target Amount - Base Amount

but based on the filtered amount values.

Is there anyway to reference the filtered values in my new measure?

The main issue (see table of data at the bottom) is that filtered rows will cancel each other out,

so i'm a bit stuck.

[TeigeGao · Accepted solution]

Hi jbarlowjb,

According to your description, my understanding is that you want to calculate the Target Amount - Base Amount, in this scenario, we can use two variable to calculate these two value, then return the Target Amount - Base Amount, please refer to the following DAX query:

Target-Base = 
var targetamount = CALCULATE(SUM(Data[data]),FILTER(ALL(Data),Data[Category] = SELECTEDVALUE('Target Category'[Category])))
var baseamount = CALCULATE(SUM(Data[data]),FILTER(ALL(Data),Data[Category] = SELECTEDVALUE('Base Category'[Category])))
return targetamount - baseamount

The result will like below:

Best Regards,

Teige