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,
I'd like to obtain the average of a previously calculed measure of minimum per group. I'll give you an example:
Student | Mark | Group |
John | 90 | A |
Linda | 65 | A |
Mark | 85 | A |
Vanessa | 90 | B |
George | 55 | B |
Harry | 70 | B |
What I'd like to obtain is, in first place:
- The minimum mark my group (for Group A it would be 65, and for group B, 55)
- Get the average of these marks (AVERAGE(65,55)=60)
I thought of getting the minimum as a calculated column, as you can see here:
Student | Mark | Group | Minimum |
John | 90 | A | 65 |
Linda | 65 | A | 65 |
Marcus | 85 | A | 65 |
Vanessa | 90 | B | 55 |
George | 55 | B | 55 |
Harry | 70 | B | 55 |
But I came across a problem: my dashboard includes slicers and, if I want to filter by Group A and John mark, I'd like to obtain 90 instead of 65. As I have read, calculated columns are generated when data is loaded and doesn't care about slicers.
So the other option is getting minimum by group as a measure and then get the average, again as a measure. The problem is, I suppose, that I can't create a measure that contains two values (the minimum for group A and the minimum for group B). In this case, how could I get my final measure: an average of minimum values by group that depends on slicers? I'm really stuck on this.
Thanks for your help.
Solved! Go to Solution.
Hi @Anonymous ,
If you want your minimun filtered by the slicer, you may create the measure instead of the calculated column.
This is the Measure vs. Calculated columns blog:
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
https://insightsoftware.com/blog/calculated-columns-vs-measures/
Back to your question, the following steps are my solution.
1.Create the Minimun measure.
Minimum = CALCULATE(MIN('Table'[Mark]),ALLEXCEPT('Table','Table'[Group]))
2.Create the Average measure.
Average = AVERAGEX('Table',[Minimum])
They can be filtered by the slicer.
You can check more details from here.
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 ,
If you want your minimun filtered by the slicer, you may create the measure instead of the calculated column.
This is the Measure vs. Calculated columns blog:
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
https://insightsoftware.com/blog/calculated-columns-vs-measures/
Back to your question, the following steps are my solution.
1.Create the Minimun measure.
Minimum = CALCULATE(MIN('Table'[Mark]),ALLEXCEPT('Table','Table'[Group]))
2.Create the Average measure.
Average = AVERAGEX('Table',[Minimum])
They can be filtered by the slicer.
You can check more details from here.
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.
@Anonymous , Not very clear , Try a measure like
AverageX(values(Table[Group]), calculate(min(Table[Mark]), filter(allselected(Table), Table[Group] = max(Table[Group]))))
or
AverageX(values(Table[Group]), calculate(min(Table[Mark]), filter(all(Table), Table[Group] = max(Table[Group]))))
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 |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |