Forum Discussion
Using values from sliced measures in other measures
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:
- I have a base data set
- I have created 'Measure 1' from the base data set, which shows the percentage of the people who have taken action Y
- I have created 'Slicers 1' (a group of slicers) which allows the user to apply filters to Measure 1 only
- I have created 'Measure 2' from the base data set, which shows the percentage of the people who have taken action Y
- I have created 'Slicers 2' (a group of slicers) which allows the user to apply filters to Measure 2 only
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
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.
3 Replies
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
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 Anonymous
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
