Calculation median, Q1 and Q2 from grouped data in a new table (descriptive statistics)

[CornelisV]

Dear all,

I'm struggling for finding a way to calculate Median, Q1 and Q2 from a table that is categorized with 'Class':

Class	Value
A	20
A	25
A	40
A	13
A	15
B	99
B	101
B	105
B	88
B	91
B	99

 Q1 and Q3 are quartiles from a dataset, where Q2 = Median.

The output should be, in a new table:

Class	Median	Q1	Q3
A	20	15	25
B	99	93	100.5

Could you please demonstrate how to do create a new table?

Best regards,

Cornelis

[v-rzhou-msft · Accepted solution]

Hi @CornelisV ,

Please try code as below to create measures.

Median = MEDIAN('Table'[Value])

Q1 = PERCENTILE.INC('Table'[Value],0.25)

Q3 = PERCENTILE.INC('Table'[Value],0.75)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CornelisV · Accepted solution]

Hello Rico Zhou,

Thank you for your support, that looks promising.

Could you please expalin me how dit you split into Class A and Class B with the Median, Q1 and Q3 output as listed above?

Best regards,

Cornelis

[CornelisV]

So, use a Quick calculation, apply like this 

Q1 = PERCENTILE.INC('Source'[Value],0.25)

and the calculation will be automatically done by group.

[Ritaf1983]

Hi @CornelisV 

You did not specify the quarter for each row, which makes the calculation logic unclear. Could you please provide the missing details to clarify the logic?

[CornelisV]

Hi @Ritaf1983 

Thank you for your message.

It is not the quartile of each row, but the quartile from a serie.

so serie with class = A have the values in order of the next row:

13  15  20  25  40

The Median = 20, perfect in the middle of a row.

Quartile 1 is 25 % of the row, so Q1 = 15

Quartile 3 is 75 % of the row, so Q3 = 25

 Does this make any sense?

Best regards,

Cornelis

[v-rzhou-msft · Accepted solution]

Hi @CornelisV ,

Please try code as below to create measures.

Median = MEDIAN('Table'[Value])

Q1 = PERCENTILE.INC('Table'[Value],0.25)

Q3 = PERCENTILE.INC('Table'[Value],0.75)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.