Forum Discussion

ApurvaKhatri's avatar
ApurvaKhatri
Helper III
7 years ago

Calculating Percentile on Measure

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 calcu...
Need Help
LivioLanzo's avatar
LivioLanzo
Solution Sage
7 years ago

ApurvaKhatri

 

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

ApurvaKhatri's avatar
ApurvaKhatri
Helper III
7 years ago

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.