Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## 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])

3 REPLIES 3
Helper IV

@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])

Helper IV

@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

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors