The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi @srikant,
Did you use the formula like this in Excel?
=PERCENTRANK.EXC(H2:H13,H2)
We can create a calculated column as below to work on that in Power BI desktop.
Column = VAR _ROW1 = CALCULATE(COUNTROWS(Table1),Table1[a]<=EARLIER(Table1[a])) VAR _ROW2 = CALCULATE(COUNTROWS(Table1),ALL(Table1)) RETURN _ROW1/_ROW2
For more details, please check the pbix as attached.
Regards,
Frank
Where are the %'s coming from?
The above screenshot is of Excel sheet where I percentrank.exc function to calculate percentile and the values that I got were between 0.1to 1.0 then I converted these number to percentages
OK, so your source data is like you show below and you want to do what with it? I don't understand the issue I guess.
My source data is column a, just with the numbers and column b shows percentile values of each number in column a. I have been trying to do the same in powerbi but it is not happening
Hi @srikant,
Did you use the formula like this in Excel?
=PERCENTRANK.EXC(H2:H13,H2)
We can create a calculated column as below to work on that in Power BI desktop.
Column = VAR _ROW1 = CALCULATE(COUNTROWS(Table1),Table1[a]<=EARLIER(Table1[a])) VAR _ROW2 = CALCULATE(COUNTROWS(Table1),ALL(Table1)) RETURN _ROW1/_ROW2
For more details, please check the pbix as attached.
Regards,
Frank
according to your example I want to made this percentile formula dynamic as measure.
from picture below i want to create a measure to show percentile of each branch_name base on principle they ve made.
Now i ve noidea what to do. thanks
Yeah, I used percentrank.exc formula
Hi @srikant,
Did you try the way as I shared?
Regards,
Frank
Hi @srikant ,
Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.
Regards,
Frank
User | Count |
---|---|
77 | |
77 | |
36 | |
30 | |
28 |
User | Count |
---|---|
106 | |
97 | |
55 | |
49 | |
46 |