Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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
User | Count |
---|---|
98 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
58 |