cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors