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.
Hi all,
I'm new to Power BI. Hoping someone might be able to help me with the below problem. I've tried to search for the answers but nothing I find seems to work. Maybe I'm not searching for the right phrases.
The background:
The problem
I now want to create 'Measure 3', which displays 'Measure 1 - Measure 2'. Measure 3 should change depending on what the user has selected in Slicers 1 and Slicers 2.
I can't seem to find the answer. Any ideas? I am Power BI/DAX novice, so please assume I know nothing about the platform when suggesting an answer.
Many thanks,
J89
Solved! Go to Solution.
Hi @J89 ,
There are many other factors that you haven't clarified clearly, such as whether your slicer fields are all from the same table? What are your specific formulas for measure 1 and measure 2? If you can, please provide some sample data (some tabular type data), as well as the desired results.
I made a sample and don't know if it matches your situation.
Sample data
Slicer1 and Slicer2 are calculated tabls.
There're no relationships among three tables.
Measure1 = CALCULATE(SUM('Table'[percentage]),FILTER(ALLSELECTED('Table'),[year]=SELECTEDVALUE(Slicer1[year])&&[action]="Y"))
Measure2 = CALCULATE(SUM('Table'[percentage]),FILTER(ALLSELECTED('Table'),[year]=SELECTEDVALUE(Slicer2[year])&&[action]="Y"))
Measure3 = [Measure1]-[Measure2]
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @J89 ,
There are many other factors that you haven't clarified clearly, such as whether your slicer fields are all from the same table? What are your specific formulas for measure 1 and measure 2? If you can, please provide some sample data (some tabular type data), as well as the desired results.
I made a sample and don't know if it matches your situation.
Sample data
Slicer1 and Slicer2 are calculated tabls.
There're no relationships among three tables.
Measure1 = CALCULATE(SUM('Table'[percentage]),FILTER(ALLSELECTED('Table'),[year]=SELECTEDVALUE(Slicer1[year])&&[action]="Y"))
Measure2 = CALCULATE(SUM('Table'[percentage]),FILTER(ALLSELECTED('Table'),[year]=SELECTEDVALUE(Slicer2[year])&&[action]="Y"))
Measure3 = [Measure1]-[Measure2]
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for taking the time to explain this so clearly with examples. Your solution worked perfectly.
I think the problems I was experiencing were two-fold.
Firstly, I wasn't using seperate tables for slicers. All the information was in one table - example below
Table 1
ID | ACTION TAKEN
(The criteria being calculated) | YEAR OF BIRTH
(The vlaues being used in the slicer |
1 | Y | 2000 |
2 | Y | 1999 |
3 | N | 1998 |
The second issue was that Measure 1 and Measure 2 were not referencing the non-existent slicer tables in their formulas.
Original incorrect formulas example:
Measure 1 = (CALCULATE(COUNT('Table 1'[ACTION TAKEN]),'TABLE 1'[ACTION TAKEN]="Y")/(COUNT('TABLE 1'[ACTION TAKEN]))
Measure 2 = (CALCULATE(COUNT('Table 1'[ACTION TAKEN]),'TABLE 1'[ACTION TAKEN]="Y")/(COUNT('TABLE 1'[ACTION TAKEN]))
Measure 3 = Measure 1 - Measure 2
My formulas now mirror those you have set out and, as I've said, it works perfectly.
Thank you
J89
For context, the intention it to allow the user to compare different groups of people who have taken action Y and see the difference in Measure 3
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 |
---|---|
113 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |