cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors