Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
105 | |
88 | |
75 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |