Calculating Percentile on Measure

[ApurvaKhatri]

Hi,

I have a measure in my powerbi report. I am trying to calculate the percentile on the measure [UnitsLeased] and table1.

However, when I apply the formula, I do not get the option of calculating the percentile for measure. Please help.

[v-piga-msft]

Hi @ApurvaKhatri,

Have you solved your problem?

If you have solved, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share your desired output.

Best  Regards,

Cherry

[LivioLanzo]

@ApurvaKhatri Hello,  please share your dataset

[ApurvaKhatri]

I wont be able to share the actual dataset. However, the dummy set is:

Sales Manager	UnitsLeased
A	2
B	5
C	7
D	9

UnitLeased is a measure.

Also, I have a date slicer that has hierarchy of Year- Month

[LivioLanzo]

@ApurvaKhatri

I won't be able to provide the exact measure without seeing the model. But in general you can do something like this:

PERCENTILEX.INC( VALUES(Sales[Sales Manager] ), [UnitsLeased], 0.9 )

you will need to adapt this to your needs

[ApurvaKhatri]

Thanks for the help. But that doesnt work as expected.

[LivioLanzo]

@ApurvaKhatri

I am not surprised. As I said the measure depends on your matrix and on your model

[ApurvaKhatri]

Measure formula:

Unit Leased = CALCULATE(DISTINCTCOUNT(view_Report_Sale_SaleManagerOverView[opportunityid]),DATESBETWEEN(view_Report_Sale_SaleManagerOverView[actualclosedate],[firstdate],[lastdate]),view_Report_Sale_SaleManagerOverView[statecode]="won",ALLEXCEPT(view_Report_Sale_SaleManagerOverView,view_Report_Sale_SaleManagerOverView[Agentname]))

Unit	agentname	createdon	actualclosedate	statecode
1	A	8/15/2018	9/6/2018	Won
1	A	7/24/2018	9/19/2018	Won
1	A	8/28/2018	9/21/2018	Won
1	A	5/1/2018	9/25/2018	Won
1	A	8/7/2018	9/25/2018	Won
1	A	8/9/2018	9/26/2018	Won
1	A	8/21/2018	9/26/2018	Won
1	A	3/9/2018	9/28/2018	Won
1	A	9/10/2018	9/28/2018	Won
1	C	8/14/2018	9/6/2018	Won
1	C	8/18/2018	9/19/2018	Won
2	J	7/27/2018	9/5/2018	Won
1	J	8/24/2018	9/6/2018	Won
1	J	8/14/2018	9/7/2018	Won
1	J	4/11/2018	9/11/2018	Won
1	J	8/13/2018	9/14/2018	Won
1	J	7/30/2018	9/21/2018	Won
1	J	8/23/2018	9/27/2018	Won
1	J	8/14/2018	9/28/2018	Won
1	N	8/9/2018	9/5/2018	Won
1	N	7/17/2018	9/6/2018	Won
1	N	7/12/2018	9/11/2018	Won
1	N	8/23/2018	9/21/2018	Won
1	N	9/11/2018	9/21/2018	Won
1	N	8/23/2018	9/27/2018	Won
1	N	9/24/2018	9/28/2018	Won
1	S	8/22/2018	9/4/2018	Won
1	S	8/3/2018	9/7/2018	Won
1	S	8/10/2018	9/7/2018	Won
1	S	8/15/2018	9/10/2018	Won
1	S	8/20/2018	9/10/2018	Won
1	S	8/28/2018	9/11/2018	Won
1	S	5/10/2018	9/13/2018	Won
1	S	6/15/2018	9/18/2018	Won
1	S	7/13/2018	9/19/2018	Won
1	S	7/27/2018	9/20/2018	Won
1	S	8/29/2018	9/20/2018	Won
1	S	9/20/2018	9/25/2018	Won
1	S	8/14/2018	9/26/2018	Won
1	S	7/26/2018	9/27/2018	Won
1	S	9/7/2018	9/28/2018	Won
1	T	8/16/2018	9/20/2018	Won
1	T	6/27/2018	9/25/2018	Won
1	T	8/30/2018	9/27/2018	Won
2	T	9/6/2018	9/28/2018	Won
1	T	9/14/2018	9/28/2018	Won
1	Z	6/27/2018	9/12/2018	Won
1	Z	8/28/2018	9/17/2018	Won
1	Z	8/17/2018	9/19/2018	Won
1	Z	8/6/2018	9/25/2018	Won
2	Z	8/16/2018	9/25/2018	Won
1	Z	9/21/2018	9/27/2018	Won

The PowerBi report would be like:

Created on is the date slicer with hierarchy year- Month.

The table would have:

AgentName     UnitLeased      Unit 75% percentile.

please let me know if this would help you resolve my issue.

[LivioLanzo]

Are you looking for something like this?

[ApurvaKhatri]

Yes.

[LivioLanzo]

You can get the file here: 

https://1drv.ms/u/s!AiiWkkwHZChHjyqWWXNtE1Qrsyre

[Anonymous]

At first clance it looks like it works, when you try other percentiles though it fails, also plotting it over time really screws it up, it's a shame because it would be really useful