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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
JamesBurke
Helper III
Helper III

Rank x

Hi , 

 

I'm struggling with a rankx Dax calculation that would rank all deivces assoicated with a specific stores name , 

 

Imagine i have 30 stores with hundreds of devices But i only want to rank one store specfically from this list . 

 

AllSlected function is useful until you drill down creating that Filtered Device to always be rank 1. 

 

Item Rank Device name =
   CALCULATE(
     RANKX(
        ALL('Emporia Devices'[Device Name]), // NOT ALLSELECTED
        [Total Kwh]),
        'Emporia Devices'[Store Name] = "KP"
   )
 
Is what i have tried with the aim it returns a ranking of all the Devices that are in use by the KP store, however it ranks all devices from all stores and ranks them the same value

Any help would be Appericated. 
 
 
6 REPLIES 6
Greg_Deckler
Super User
Super User

@JamesBurke This may help: To *Bleep* with RANKX! - Microsoft Fabric Community

 

Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Store NameDevice Usages
Store 1KP 113
Store 1 KP 218
Store 1KP 315
Store 2 SD 11
Store 2SD 2 32
Store 2SD 314

 

Expected outcome : 

 

Rank 1 : KP 2 

Rank 2 : KP 3 

Rank 3 : KP 1

 

Essentially i just want the ability to filter devices that start with KP from a list of Devices and Rank these based on usages. 

@JamesBurke So like this?

Rank = RANKX( FILTER( ALL('Table'), LEFT([Device],2) = "KP" ), CALCULATE(SUM([Usages])),, DESC )


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

 

Rank = 
RANKX(
FILTER( ALL('Emporia Devices'),
LEFT('Emporia Devices'[Device Name],2) = "KP" ),
CALCULATE(SUM('Emporia Device Usage'[Usages KWH])),, DESC )
 
This does not filter the device names to only rank KP Stores, it ranks all and not by Usages for some reason. 

@JamesBurke How about this?

Rank = 
IF( LEFT('Emporia Devices'[Device Name],2) = "KP",
RANKX(
FILTER( ALL('Emporia Devices'),
LEFT('Emporia Devices'[Device Name],2) = "KP" ),
CALCULATE(SUM('Emporia Device Usage'[Usages KWH])),, DESC ),
BLANK()
)


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

 

 CALCULATE(RANKX(
        ALL('Emporia Devices'[Device name KP]), // NOT ALLSELECTED
        [Total Kwh])
    )
 
I've made conditonal column and use the above Measure , is there a simple way to not include blanks in the rankings ?
 
thanks !

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

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

Jan NL Carousel

Fabric Community Update - January 2025

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