Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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:

 

2018-10-08_9-15-30.png

 

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
LivioLanzo
Solution Sage
Solution Sage

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

 

 

2018-10-08_15-58-39.jpg

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

7 REPLIES 7
LivioLanzo
Solution Sage
Solution Sage

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

 

 

2018-10-08_15-58-39.jpg

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Anonymous
Not applicable

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

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Anonymous
Not applicable

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.

Try to upload it in your Dropbox, Onedrive etc

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Anonymous
Not applicable

 
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.