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
CarlsBerg999
Helper V
Helper V

Silly ranking question

Hi,

 

I basically i have two columns:

 

Linkedin 500

Facebook 400 

Twitter 350

Others 370

 

I want these values to be ranked so that anything that is not "Others" is ranked and presented in a descending order, but Others is presented as last. For example, if i rank the above alphabetically or by value, "Others" would be presented in the middle. This is wrong, it needs to be last. How do i rank everything except "Others"?

 

Thank you!

1 ACCEPTED SOLUTION

@CarlsBerg999,

 

Please confirm that you created measures, not calculated columns.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
DataInsights
Super User
Super User

@CarlsBerg999,

 

Try the following measures:

 

Total Amount = SUM ( RankTest[Amount] )

Company Rank = 
VAR vAllValues =
    ALL ( RankTest[Company] )
VAR vValuesToRank =
    FILTER ( vAllValues, RankTest[Company] <> "Others" )
VAR vRowCount =
    COUNTROWS ( vValuesToRank )
VAR vResult =
    RANKX ( vValuesToRank, [Total Amount],, DESC, DENSE )
RETURN
    IF ( MAX ( RankTest[Company] ) = "Others", vRowCount + 1, vResult )

 

DataInsights_0-1602623903920.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I'm getting the same Rank (1) for all of these. I think the formula is making SUM for entire "Amount" column which is the same for all values. Am i doing something wrong? In the example, the Total Amount = SUM(RankTest[Amount])=1620 for all values. Therefore the rank is the same for all. 

@CarlsBerg999,

 

Please confirm that you created measures, not calculated columns.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.