Rank x

[JamesBurke]

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. 

[Greg_Deckler]

@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.

[JamesBurke]

Store Name	Device	Usages
Store 1	KP 1	13
Store 1	KP 2	18
Store 1	KP 3	15
Store 2	SD 1	1
Store 2	SD 2	32
Store 2	SD 3	14

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. 

[Greg_Deckler]

@JamesBurke So like this?

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

[JamesBurke]

@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. 

[Greg_Deckler]

@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()
)

[JamesBurke]

@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 !