cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
s45kougo
Helper I
Helper I

Calculate median of aggregated data

Hi,

 

Hoping someone can help as I can't find any information on this...

 

I want to calculate the median price for some pre-aggregated data - I don't have access to the line-level data.

A simplified table looks like this:

Total SpendQuantityPrice
2555
20210
40104

This should give a median of 4.  But how do I write that calculation?

 

Thanks in advance

1 ACCEPTED SOLUTION
Greg_Deckler
Super User IV
Super User IV

@s45kougo - Here is a much more elegant solution, Page 32, PBIX Table (32) attached below sig.

Measure 32 = 
    VAR __Table =
        GENERATE(
        'Table (32)',
        VAR __Quantity = [Quantity]
        RETURN GENERATESERIES(1,__Quantity,1)
    )
RETURN
    MEDIANX(__Table,[Price])

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Greg_Deckler
Super User IV
Super User IV

@s45kougo - Here is a much more elegant solution, Page 32, PBIX Table (32) attached below sig.

Measure 32 = 
    VAR __Table =
        GENERATE(
        'Table (32)',
        VAR __Quantity = [Quantity]
        RETURN GENERATESERIES(1,__Quantity,1)
    )
RETURN
    MEDIANX(__Table,[Price])

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




View solution in original post

Oh wow, you are amazing , thank you *so* much! This works perfectly. I thought it must be possible, but I had no idea how to disaggregate a table. Hope this helps someone else too in the future 🙂

Greg_Deckler
Super User IV
Super User IV

@s45kougo - There may be a more elegant solution and I will think about it but for this you could brute force it:

Measure = 
  VAR __Table25 = 
    ADDCOLUMNS(
      GENERATESERIES(1,MAXX(FILTER('Table',[Total Spend]=25),[Quantity]),1),
      "Price",MAXX(FILTER('Table',[Total Spend]=25),[Price])
    )
  VAR __Table20 = 
    ADDCOLUMNS(
      GENERATESERIES(1,MAXX(FILTER('Table',[Total Spend]=20),[Quantity]),1),
      "Price",MAXX(FILTER('Table',[Total Spend]=20),[Price])
    )
  VAR __Table40 = 
    ADDCOLUMNS(
      GENERATESERIES(1,MAXX(FILTER('Table',[Total Spend]=40),[Quantity]),1),
      "Price",MAXX(FILTER('Table',[Total Spend]=40),[Price])
    )
  VAR __Table = UNION(__Table25,__Table20,__Table40)
RETURN
  MEDIANX(__Table,[Price])

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




amitchandak
Super User IV
Super User IV

@s45kougo , In case this data is already aggregated. You can average

 

Avg = Divide(Sum(Table[Total Spend]),sum(Table[Quantity]))

Which will be five

You can try MEDIAN( Table[Price] ) 

But I doubt you can get 4



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak thanks for trying, but I do want the actual median of 4... I'm guessing that somehow I have to calculate a table that dis-aggregates, but not sure how to do this.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors