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.
Hello all,
I have a page where I use different table visualization/slicer combination to display a series of performance measures for my company. One slicer interacts with one and only one table visualization. In the end, I want to get all those percentages with their current slicer filter to measure their average. However, I can't do so straight away because for the final table visualization, in which I display the averages, all the slicers are interacting, producing a different calculation than the one I want. With this in mind, I can't use Edit Interactions for such scenario. What I have to do is to find a DAX way to calculate the average and making sure that each measure is filtered according to the one slicer, ignoring the others. (I am sorry for hiding all the information)
I have been struggling with ignoring some slicer selections in a DAX Measure. I have tried to use the ALL, ALLEXCEPT, IN VALUES but without success. Are those functions expected to work in such scenario? Is there a straightforward solution or a workaround to solve my problem?
The example of the code I'm using (without wrapping in CALCULATE with the above mentioned functions). The idea was to use something like ALLEXCEPT(Table, Table[Column referenced for that percentage]).
Sum Evaluation Average = IF([Percentage 1] <> BLANK(), [Percentage 1], 0) + IF([Percentage 2] <> BLANK(), [Percentage 2], 0) + IF([Percentage 3] <> BLANK(), [Percentage 3], 0) + IF([Percentage 4] <> BLANK(), [Percentage 4], 0) + IF([Percentage Within 5] <> BLANK(), [5], 0) + IF([Percentage Within 6] <> BLANK(), [Percentage 6], 0) + IF([Percentage Within 7] <> BLANK(), [Percentage 7], 0)
Kind Regards
Solved! Go to Solution.
Hi @mjmt1994
Please look at my test for example
Create measures
per is the percentage for each row per = DIVIDE(MAX([value]),MAX([total])) average per is the average percentage as you expected, average per = SUMX(ALLEXCEPT(Sheet2,Sheet2[category]),[per])/
CALCULATE(DISTINCTCOUNT(Sheet2[flag]),ALL(Sheet2))
The final table should not be filtered by any slicer on the page.
Best Regards
Maggie
Hi @mjmt1994
Please look at my test for example
Create measures
per is the percentage for each row per = DIVIDE(MAX([value]),MAX([total])) average per is the average percentage as you expected, average per = SUMX(ALLEXCEPT(Sheet2,Sheet2[category]),[per])/
CALCULATE(DISTINCTCOUNT(Sheet2[flag]),ALL(Sheet2))
The final table should not be filtered by any slicer on the page.
Best Regards
Maggie
Hi,
Thanks for the quick response!
That's basically it, yes.
Is there a solution for it?
Kind Regards
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |