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

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

Reply
Anshenterprices
Helper IV
Helper IV

How to calculate percentage ranking

DateCategory1DaySalesRankpercent rank
4/6/2021Equity1234100
4/6/2021Equity345375
4/6/2021Equity567250
4/6/2021Equity789125
4/6/2021Commodity4532100
4/6/2021Commodity564150
4/6/2021Currency2341100
4/5/2021Equity256375
4/5/2021Equity04100
4/5/2021Equity345250
4/5/2021Equity754125
4/5/2021Commodity6831100
4/5/2021Commoditynull  
4/5/2021Currency2463300


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
amitchandak
Super User
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])

View solution in original post

3 REPLIES 3
Anshenterprices
Helper IV
Helper IV

@amitchandak Thank you so much

amitchandak
Super User
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])

@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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.