Dynamic Ranking - Measure

[Anonymous]

Hi,

I'm struggling to do dynamic ranking which changes based on my filters.  I've managed to do a ranked column which works fine - just can't get my head around creating a measure to do the same.

My data is like this:

List of office locations with a % attached:

e.g

Name                           %                 Rank

London                        10.2%            4

Manchester                  6.7%              2

Birmingham                 4.8%              1

Leeds                            8.3%              3

Area Table

Area 1 = Manchester, Leeds

I am then connected to an area table so if I filter on for example area 1, it returns Manchester and Leeds and I want the rank to recalculate.  I'm connected to a folder data source and have created a new table for the latest figures using lastdate.  My files within the folder are just excel which are added to monthly and contain a row for each office location.

I'm trying to write the measure on the newly created last date table.

Thanks in advance for any help!

[Anonymous]

Please ignore the previous message, I've fixed it by using the office name from the actual table as opposed the the area table!

[Anonymous]

Hi,

Sorry in follow up to this question, when I drop the no calculate ranking into my table it resets all other columns until a slicer is selected, for example, I can see a list of office locations but all the %'s return 0% and ranking returns 1.

Any help would be great!

Many thanks

[Anonymous]

Brill, thank you so much! The no calculate one works perfectly!

I'd tried with the calculate one and it was returning the sum of the filter - e.g. I have 50 offices it dispayed 50 on every line and then if I filtered to 10 it would show that.  I thought I was doing something wrong so glad we got the same results for that one!

Thanks again for all your help! 

[Greg_Deckler · Accepted solution]

@Anonymous You may like this: To *Bleep* with RANKX! - Microsoft Power BI Community

Here is the CALCULATE approach:

Rank Measure = RANKX(ALLSELECTED('Table'),CALCULATE(SUM('Table'[%])),,DESC)

And the No CALCULATE approach:

Rank No Calculate Measure = 
    VAR __Name = MAX('Table'[Name])
    VAR __Table = 
        ADDCOLUMNS(
            ALLSELECTED('Table'),
            "__Rank", RANKX(ALLSELECTED('Table'),[%],,DESC)
        )
    VAR __Result = MAXX(FILTER(__Table,[Name] = __Name),[__Rank])
RETURN
    __Result