Median of distrubution i DAX

[Anonymous]

Dear community!

I want to calculate the median age of all cars in this this dataset but I can't get a grip of how to do it in DAX. It's like calculating the median of a distribution function.  For example: The answer here for Mazda is three months. (total number of Mazda+ 1)/2 = 262 which corresponds to three months (larger than 232 but less than 367). But I also need to do the calculation for all brands.

 I would guess the answer involves MedianX and Summarize but hopefully someone now how to put them togheter as well 🙂

BR Thomas

Brand	Age of car in months	Number of cars	Cumulative number of Cars
Mazda	0	100	100
Mazda	1	50	150
Mazda	2	82	232
Mazda	3	135	367
Mazda	4	20	387
Mazda	5	61	448
Mazda	6	75	523
Kia	0	32
Kia	1	41
Kia	2	80
Kia	3	120
Kia	4	130
Kia	5	50

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below measures and the attached pbis file.

cumulate numbers : =

VAR currentbrand =

MAX ( Data[Brand] )

VAR currentmonth =

MAX ( Data[Age of car in months] )

RETURN

CALCULATE (

SUM ( Data[Number of cars] ),

FILTER (

ALL ( Data ),

Data[Brand] = currentbrand

&& Data[Age of car in months] <= currentmonth

)

)

Expected result : =

VAR targetnumber =

( SUM ( Data[Number of cars] ) + 1 ) / 2

VAR newtable =

FILTER (

ADDCOLUMNS (

SUMMARIZE ( data, Data[Age of car in months] ),

"@cumulatenumbers", [cumulate numbers :]

),

[@cumulatenumbers] > targetnumber

)

RETURN

IF (

HASONEVALUE ( Data[Brand] ),

MINX ( newtable, Data[Age of car in months] )

)

[Anonymous]

Many thanks Jihwan_Kim, kudos!

I have tried it out and it works perfectly fine 👍
Just one adjustment, if you are to use it witout the column "brand" (i.e Median of all cars) you have to change:

IF (

HASONEVALUE ( Data[Brand] ),

MINX ( newtable, Data[Age of car in months] )

)

in measure "Expected result" to just: 

MINX ( newtable, Data[Age of car in months] )