How to get Percentiles with group by

[Chenxu]

I have a table with 3 columns: UserName, StartDate, Duration. I want to get the percentiles of Duration for each user on each day. How to write the query?

[v-yuta-msft · Accepted solution]

Hi Chenxu,

For test, I have made a sample table like this:

 

To get the percentiles of Duration for each user on each day, you can create a calculate column and use DAX formula below:

Percentile =

VAR Duration_Sum =

SWITCH(Table1[UserName],

    "A", CALCULATE(SUM(Table1[Duration]), FILTER(Table1, Table1[UserName] = "A")),

    "B", CALCULATE(SUM(Table1[Duration]), FILTER(Table1, Table1[UserName] = "B")),

    "C", CALCULATE(SUM(Table1[Duration]), FILTER(Table1, Table1[UserName] = "C")))

RETURN

CALCULATE(DIVIDE(MAX(Table1[Duration]), Duration_SUm), FILTER(Table1, Table1[UserName] = EARLIER(Table1[UserName]) && Table1[StartDate] = EARLIER(Table1[StartDate])))

The result is like below:

 

Sample PBIX here:

https://www.dropbox.com/s/sb5v6zpbq1kjlc5/For%20Chenxu.pbix?dl=0

Regards,

Jimmy Tao