cancel
Showing results for
Did you mean:
Helper III

## How to calculate percentage ranking

 Date Category 1DaySales Rank percent rank 4/6/2021 Equity 123 4 100 4/6/2021 Equity 345 3 75 4/6/2021 Equity 567 2 50 4/6/2021 Equity 789 1 25 4/6/2021 Commodity 453 2 100 4/6/2021 Commodity 564 1 50 4/6/2021 Currency 234 1 100 4/5/2021 Equity 256 3 75 4/5/2021 Equity 0 4 100 4/5/2021 Equity 345 2 50 4/5/2021 Equity 754 1 25 4/5/2021 Commodity 683 1 100 4/5/2021 Commodity null 4/5/2021 Currency 246 3 300

I want to calculate rank and percent rank

In report i have slicer Date, Category

Ranking is based on 1daysales by the selection of date and category
percent rank forumula= ranking*100/counta(1daysales)

Note :- if its blank dont assign rank and percent rank

1 ACCEPTED SOLUTION
Super User

@Anshenterprices , Create two measures like

Rank = rankx(filter(allselected(Table[DAte], Table[Category]), [Category] =max([category]) ), calculate(sum(table[1DaySales])))

count Day = calculate(count(Table[Date]), filter(allselected(Table), Table[Date] = max(Table[Date]) && Table[category] = max(Table[category]) ))

and then %

% = divide([Rank], [count day])

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
3 REPLIES 3
Helper III

@amitchandak Thank you so much

Super User

@Anshenterprices , Create two measures like

Rank = rankx(filter(allselected(Table[DAte], Table[Category]), [Category] =max([category]) ), calculate(sum(table[1DaySales])))

count Day = calculate(count(Table[Date]), filter(allselected(Table), Table[Date] = max(Table[Date]) && Table[category] = max(Table[category]) ))

and then %

% = divide([Rank], [count day])

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Helper III

@amitchandak  thank you
forgot to mentioned in the post, In a table there is also one column i.e. online/offline category ,
I have used online/offline category as a Slicer in the report,but dont want seperate raking and percent rank  once i select online or offline category. it should be same in both ways

Announcements

#### Launching new user group features

Learn how to create your own user groups today!