cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PhilSmith
Frequent Visitor

How To write Median of subtotaled values

I am not sure how to go about this. The table is named Table, with two columns, Category and Rating.  If my data was:
Category Rating
A              2
B              3
C             4

SimpleMedian = Calculate(Median(Table.Rating))  The SimpleMedian here is 3. Easy.

Instead I have for Data:

A      2
A      6
A      4
B      1
B      8
B      7
C     3
C     6
C    20

SimpleSum = Calculate(SUM(table.Rating))   which gives me the values below: 
A    12
B    16
C    29

The median of that result is 16, but how do I create the measure(s) to get that result?  Median likes columns, not measures...

Ultimately, for a function that requires a column, How do I get it to work with a calculated aggregate measure instead?

Thanx

 

Phil

 

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

create a calculated table

 

 

Table 2 = SUMMARIZE('Table','Table'[Category],"Rating",sum('Table'[Rating]))

 

 

Add the median measure

 

med = MEDIAN('Table 2'[Rating])

 

Or if you want to be cute you can do the calculated table as part of the measure

med = MEDIANX(SUMMARIZE('Table','Table'[Category],"Rating",sum('Table'[Rating])),[Rating])

 

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

create a calculated table

 

 

Table 2 = SUMMARIZE('Table','Table'[Category],"Rating",sum('Table'[Rating]))

 

 

Add the median measure

 

med = MEDIAN('Table 2'[Rating])

 

Or if you want to be cute you can do the calculated table as part of the measure

med = MEDIANX(SUMMARIZE('Table','Table'[Category],"Rating",sum('Table'[Rating])),[Rating])

 

A Calculated table doesn't work, because I need dynamic results.  However, using it in a measure like that was the perfect answer.  Thank you very much!

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.