- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Median of a level of detail expression

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

rranieri

Regular Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-08-2018
06:18 AM

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

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

LivioLanzo

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-08-2018
06:58 AM

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

)

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

**Proud to be a Datanaut! **

7 REPLIES 7

LivioLanzo

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-08-2018
06:58 AM

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

)

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

**Proud to be a Datanaut! **

rranieri

Regular Visitor

Re: Median of a level of detail expression

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-08-2018
07:31 AM

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

LivioLanzo

Super User

Re: Median of a level of detail expression

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-08-2018
07:42 AM

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! **

rranieri

Regular Visitor

Re: Median of a level of detail expression

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-08-2018
10:35 AM

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.

LivioLanzo

Super User

Re: Median of a level of detail expression

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-08-2018
10:45 AM

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! **

rranieri

Regular Visitor

Re: Median of a level of detail expression

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-08-2018
10:54 AM

rranieri

Regular Visitor

Re: Median of a level of detail expression

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-08-2018
11:07 AM

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