Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Say I have data like this:
Date | Account | Statistic | Value |
30/06/2017 | 1 | Alpha | 0.85 |
30/06/2017 | 2 | Alpha | 0.34 |
30/06/2017 | 1 | Beta | 0.52 |
30/06/2017 | 2 | Beta | 0.29 |
30/06/2017 | 1 | Gamma | 0.14 |
30/06/2017 | 2 | Gamma | 0.25 |
31/07/2017 | 1 | Alpha | 0.35 |
31/07/2017 | 2 | Alpha | 0.23 |
31/07/2017 | 1 | Beta | 0.51 |
31/07/2017 | 2 | Beta | 0.26 |
31/07/2017 | 1 | Gamma | 0.16 |
31/07/2017 | 2 | Gamma | 0.90 |
31/08/2017 | 1 | Alpha | 0.88 |
31/08/2017 | 2 | Alpha | 0.20 |
31/08/2017 | 1 | Beta | 0.76 |
31/08/2017 | 2 | Beta | 0.79 |
31/08/2017 | 1 | Gamma | 0.83 |
31/08/2017 | 2 | Gamma | 0.66 |
Now imagine I have two bar charts (one per Account), with Date on X and Value on Y, and the different Statistics as different bars.
[In reality there are a lot more Accounts, and there are two slicers to select the two Accounts we want to compare.]
How can I get a third chart that shows the Value difference between the two, again per Date and Statistic?
I started by creating a duplicate of the dataset and now want to do something like https://community.powerbi.com/t5/Desktop/How-to-calculate-difference-after-different-filter-settings... - but there is no sensible way to relate the table with its duplicate.
There must a way to do this surely?
Solved! Go to Solution.
Hi @olik,
You can try the below method as well!!! FYI, I have the data that you have provided for this purpose
The Following are the steps.
1. Have 2 copies of your data (namely Table1 and Table2)
2. Join the tables based on Date Column
3. Create the following 2 measures
In Table1
Measure 1 = CALCULATE(SUM(Table1[Value]), FILTER(Table1, Table1[Account] = SELECTEDVALUE(Table1[Account])))
In Table2
Measure 2 = CALCULATE(SUM(Table2[Value]), FILTER(Table2, Table2[Account] = SELECTEDVALUE(Table2[Account])))
Create another measure to find the difference between the measures
Measure = [Measure 1] - [Measure 2]
Relationship, more likely to look like (not necessarily have to be same)
My Output looked as shown below
As you change the accounts in your slicer, you value changes correspondingly....
Hope this helps you get what you needed!!!
regards,
Thejeswar
Thanks a lot! I used your suggestion and made the formula even easier:
Result = SUM(Table1[Value]) - CALCULATE(SUM(Table1[Value]), FILTER(ALL(Table1[Account]),Table1[Account] = SELECTEDVALUE(Table2[Account])))
That way I only need one measure, and technically I don't even need the full duplicate table... Just another list of accounts would do!
Do you see any issues with my approach? Still very new to the whole DAX coding!
Hi @olik,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
Hi @olik,
You can try the below method as well!!! FYI, I have the data that you have provided for this purpose
The Following are the steps.
1. Have 2 copies of your data (namely Table1 and Table2)
2. Join the tables based on Date Column
3. Create the following 2 measures
In Table1
Measure 1 = CALCULATE(SUM(Table1[Value]), FILTER(Table1, Table1[Account] = SELECTEDVALUE(Table1[Account])))
In Table2
Measure 2 = CALCULATE(SUM(Table2[Value]), FILTER(Table2, Table2[Account] = SELECTEDVALUE(Table2[Account])))
Create another measure to find the difference between the measures
Measure = [Measure 1] - [Measure 2]
Relationship, more likely to look like (not necessarily have to be same)
My Output looked as shown below
As you change the accounts in your slicer, you value changes correspondingly....
Hope this helps you get what you needed!!!
regards,
Thejeswar
Hello, I have 2 tables (coming from the same data source, I duplicated the tables and filtered by "version date"). I want to compare the data between them. Is it possible to use a DAX Formula to do so?
Thanks a lot! I used your suggestion and made the formula even easier:
Result = SUM(Table1[Value]) - CALCULATE(SUM(Table1[Value]), FILTER(ALL(Table1[Account]),Table1[Account] = SELECTEDVALUE(Table2[Account])))
That way I only need one measure, and technically I don't even need the full duplicate table... Just another list of accounts would do!
Do you see any issues with my approach? Still very new to the whole DAX coding!
Hi @olik,
Your re-did DAX looks fine.
I think the new DAX that you have written should be good enough to get the difference
Regards,
Thejeswar
I think I have found a way, using an additional measure based on SELECTEDVALUE parameters and then defined as such:
CALCULATE
(
AVERAGE(Table1[Value]),
FILTER(
ALL(Table1[Account]),
[Account]=Table2[Selected Account]
)
)
Surely that can't be the best way though?