The basics of ranking...

[themissingelf]

I'm a complete DAX novice (in fact, less than a novice...) but I think I need to find out more about this new language... Starting with ranking...

I have a table something like this:

STORE                 DEPARTMENT                SPACE            SALES            MARGIN

-------------------------------------------------------------------------------------

BRANCH A          TOYS                             1000               30000             15000

BRANCH A          HOMEWARES                 500                 5000               2500

BRANCH A          BEDDING                      2000                40000            20000

BRANCH B          TOYS                             1000               30000             15000

BRANCH B          HOMEWARES                 500                 5000               2500

BRANCH B          BEDDING                      1000                25000            12500

In a Power BI report I want to display the rank of Branch B based on the total space as follows:

STORE                TOTAL SPACE        RANK

BRANCH B              2500                     2

I would like this to be calculated based on a Report Level filter or slicer so the list of branches can be varied and the rank changes accordinlgy.

Grateful for help to get me started!

Thanks

[v-haibl-msft · Accepted solution]

@themissingelf

You can try with following Measure formula.

Rank = 
RANKX ( ALLSELECTED ( Table1[STORE] ), CALCULATE ( SUM ( Table1[SPACE] ) ) )

Best Regards,

Herbert

[fred2711]

Thanks for the solution. It works perfectly.

What if you have 2 different tables. I have one table only for my Brands/Stores, and one for Space. And I do not want the RANK to change under any circumstance, whether someone selects a month or brands.

[themissingelf]

Thank you. That's exactly the output I want; however, when I use the formula I'm getting getting a number for each branch that is in the hundreds (I think it may be counting the number of instances of the branch in the data)...

[themissingelf]

Confirmed. It is counting the number of rows for each branch. Perplexed...

[themissingelf]

Oh, I seem to have accidentally made it work. It's appearing in a different place to my previous attempts so can only assume I've inadvertantly corrected a previous error in my attempts.

[v-haibl-msft]

@themissingelf

It seems that you have solved the problem now. You can mark the right answer as solution to close this thread.

Best Regards,

Herbert

[lalthan]

Try below formula. I am not a big fan of rankx and topN DAX formula as I find them to be very slow once the list of items is in tens of thousands and above.

Assuming [Total Space] is a measure i.e. calculated measure li

calculate(rankx(all('Table Name'[Store]),[Total Space])

all('Table Name'[Store]) - This will ignore the filter even if you filter on a single store