Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Dynamic Ranking - Measure

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!

  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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! 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
    Anonymous
    Not applicable
    3 years ago

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