Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PhilSmith
Helper I
Helper I

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors