Average over median values, which gets data from a measure

[Tempo]

So I have a complex measures wich calculates the values, which you can see in the table. This specific measures uses other measures to calculate these values.

Year	Enterprise_1	Enterprise_2
2020	-0.17
2021	-0.39	-0.3
2022	-0.57	-0.6

Now what I want to do, is to calculate the median of this value per enterprise and then calculate the average of this median values. So the calculation would look something like this:

Median Enterprise_1: MEDIAN(-0.17,-0.39,-0.57) = -0.39
Median Enterprise_2: MEDIAN(-0.3,-0.6) = -0.45

Average over both enterprises: AVERAGE( -0.39, -0.45) = -0.41667

That is the result, which I need.

Attempt

My first try was to use the GROUPBY-Function, but I always got the error:
"Function 'GROUPBY' scalar expressions have to be Aggregation functions over CurrentGroup(). The expression of each Aggregation has to be either a constant or directly reference the columns in CurrentGroup()." I think the problem there was, that I have several measures which are needed for the basic values (values in the table). I did use MEDIANX()-Function.

I also tried to add all the measure to a separate table. I added the calculated MedianColumn like:
MEDIANColumn = MEDIAN([Revenue Delta Median])

Then I tried to add a measure to calculate the average over that:

AverageMeasure =CALCULATE(AVERAGE('Table'[MEDIANColumn]),FILTER('Table','Table'[MEDIANColumn]<>BLANK()))

The Result which I get is always blank.

Does somebody can help me, how to solve that (number of enterprises is dynamically)?

[Anonymous · Accepted solution]

Hi @Tempo ,

You could create measures like:

Median Enterprise_1 = CALCULATE(MEDIAN('Table'[Enterprise_1]),ALLSELECTED('Table'))

Median Enterprise_2 = CALCULATE(MEDIAN('Table'[Enterprise_2]),ALLSELECTED('Table'))

Then get the average

Average over both enterprises = DIVIDE([Median Enterprise_1]+[Median Enterprise_2],2)

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.