Calculate average of minimum by group

[Anonymous]

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.

[Anonymous · Accepted 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.

[amitchandak]

@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]))))