cancel
Showing results for
Did you mean:
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.

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
Super User

create a calculated table

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

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

2 REPLIES 2
Super User

create a calculated table

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

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

Frequent Visitor

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!

Announcements

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

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

#### Business Application LATAM Summit 2023

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

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

Top Solution Authors
Top Kudoed Authors