cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Median of a level of detail expression

Hello,

I need some help finding the median of a certain set of data.

The data involves level of detail expressions. A simplification of the data is below:

So for the calendar year 2018 there is a division, within a division there are lines of products. One line of product can belong to different divisions. The total sum of the sales for the product line within the divison for the year is shown. It is worth noting that there are many other levels of detail, after product line is class then family then individual material then individual sales order. However, I am only interested in looking at the product line.

I want a fixed median for each division considering only the sum of the sales for the product lines. For example, the median for division 4A should be \$1,600. I firstly set the sum of sales at the product line level for each divsion using the allexcept filter. However, when I try this for the median I can only use the sales (not sum of sales since the sum of sales becomes an expression and median only works with a column), and I end up taking the median of the materials within the product lines which are not accurate - so the calculated column method doesn't work. I also tried the summation function for another table but I can't link the median to the associated division. I've also tried the medianX function but again this didn't work.

Any way I can fix the median of the product line at the divsion level?

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Median of a level of detail expression

Are you looking to do it via a calculated column? or in a table/matrix?

via calculated column you can do it like this, if I understood correctly your requirements

=
CALCULATE (
MEDIANX (
SUMMARIZE ( Data, Data[Line] ),
CALCULATE ( SUM ( Data[Net Sales] ) ) * 1.0
),
ALLEXCEPT ( Data, Data[Cal Year], Data[Division] )
)

Proud to be a Datanaut!

7 REPLIES 7
Super User

## Re: Median of a level of detail expression

Are you looking to do it via a calculated column? or in a table/matrix?

via calculated column you can do it like this, if I understood correctly your requirements

=
CALCULATE (
MEDIANX (
SUMMARIZE ( Data, Data[Line] ),
CALCULATE ( SUM ( Data[Net Sales] ) ) * 1.0
),
ALLEXCEPT ( Data, Data[Cal Year], Data[Division] )
)

Proud to be a Datanaut!

Regular Visitor

## Re: Median of a level of detail expression

This did work for the dataset that I sent. However, when I apply the formula to the larger dataset I get different values for each prodcut line when I use this column. Yet when I change the value of the column to AVERAGE instead of SUM it works perfectly. Is there a way I can set this calculated column to show only the average and not the sum fo further calclautions?

Thanks

Super User

## Re: Median of a level of detail expression

I believe there is but I would like to see a sample of the dataset on which it does not work, could you show me a sample? Also if you could post something that I can copy instead of an image this would help

Proud to be a Datanaut!

Regular Visitor

## Re: Median of a level of detail expression

Sure, how exactly can I send it to you? The information is over the 20,000 character limit and I've editted it down as much as I can.

Super User

## Re: Median of a level of detail expression

Proud to be a Datanaut!

Regular Visitor

Regular Visitor

## Re: Median of a level of detail expression

So to fix the Sum vs Average error I created a new measure that is the max(formulafrombefore) and that fixes the number to the division level and works!

Thank you