cancel
Showing results for
Did you mean:
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 Spend Quantity Price 25 5 5 20 2 10 40 10 4

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

1 ACCEPTED SOLUTION
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])``````

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

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

Proud to be a Super User!

5 REPLIES 5
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])``````

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

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

Proud to be a Super User!

Helper I

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 🙂

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 =
GENERATESERIES(1,MAXX(FILTER('Table',[Total Spend]=25),[Quantity]),1),
"Price",MAXX(FILTER('Table',[Total Spend]=25),[Price])
)
VAR __Table20 =
GENERATESERIES(1,MAXX(FILTER('Table',[Total Spend]=20),[Quantity]),1),
"Price",MAXX(FILTER('Table',[Total Spend]=20),[Price])
)
VAR __Table40 =
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])``````

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

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

Proud to be a Super User!

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

Proud to be a Super User!

Helper I

@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.

Announcements

#### 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.

#### Microsoft named a Leader in The Forrester Wave

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

#### 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