Return the median value from results calculated by a measure...

[Matty]

Hi,

I've read that MEDIAN and AVERAGE, etc., can only be calculated on data in columns rather than on values returned by a measure. Is this the case or is there a workaround?

Cheers,

Matty

 

[63falcondude]

What are values returned by a measure? More specifically, what is a "measure"?

Can you share an example for us to go off of?

 

[Matty]

Hi,

The measure calculates the coefficient of variation, represented as a %, for each of the products being assessed. I want to return the median of all of the scores (%s), that is to say the midpoint where half of the products are above the line and half are below the line.

Make sense?

Cheers,

Matty

 

[63falcondude]

Hi,

The measure calculates the coefficient of variation, represented as a %, for each of the products being assessed. I want to return the median of all of the scores (%s), that is to say the midpoint where half of the products are above the line and half are below the line.

Make sense?

Cheers,

Matty

Where are those scores located? If they are in A1:A10, you can use a formula such as =MEDIAN(A1:A10)

It doesn't matter that the values in A1:A10 are resulting from formulas.

 

[Matty]

Where are those scores located? If they are in A1:A10, you can use a formula such as =MEDIAN(A1:A10)

It doesn't matter that the values in A1:A10 are resulting from formulas.

I think you've misunderstood the issue; it's actually a DAX formula I'm after, not a standard Excel formula.

Hopefully someone can assist.

Cheers,

Matty

 

[63falcondude]

I think you've misunderstood the issue; it's actually a DAX formula I'm after, not a standard Excel formula.

Hopefully someone can assist.

Cheers,

Matty

I did not notice which sub-forum this was in. I have no experience using Power BI.

Hopefully someone else can assist you.

 

[gazpage]

You'll probably want to use an iterator, MEDIANX or AVERAGEX.

https://powerpivotpro.com/2010/02/sumx-the-5-point-palm-exploding-fxn-technique/

This blog post shows an example with SUMX, but the principles are the same.

Obviously, a measure is calculated in a filter context so you need to recreate that. Say you have a list of products, A, B and C, that you put in the row field and a measure to put in the Values field you'd need something like.

AVERAGEX(
VALUES(Table[Product]),
[Measure]
)

 

[Matty]

You'll probably want to use an iterator, MEDIANX or AVERAGEX.

https://powerpivotpro.com/2010/02/sumx-the-5-point-palm-exploding-fxn-technique/

This blog post shows an example with SUMX, but the principles are the same.

Obviously, a measure is calculated in a filter context so you need to recreate that. Say you have a list of products, A, B and C, that you put in the row field and a measure to put in the Values field you'd need something like.

AVERAGEX(
VALUES(Table[Product]),
[Measure]
)

That seems to do the job, gazpage. Thanks again for coming to the rescue!

Matty

 

[Matty]

Thanks for the link, too. I will have a good read through tomorrow.

Matty

 

[Matty]

Hi,

Another related question:

If I wanted to pass the value returned by the above measure to an IF formula, how would I do this? I've tried a combination of things, but the syntax isn't quite right.

Cheers,

Matty