Forum Discussion

wilson_smyth's avatar
wilson_smyth
Post Patron
9 years ago
Solved

display Rank on a Card visualization

I have a measure that gives the rank of a store, based on its sales against other stores.

I want to just display two pieces of info, the currently selected stores rank & the total number of stores.


I can display the rank in a table with all stores showing, but if i try to do it in a card, or just selecting my single store, i get a rank of 1.

 

Is it even possible to display a specific rank on a card?

Currently the only thing i can think of is storing the rank in a calc column but this is just not dynamic enough.

  • Hi wilson_smyth,

     

    I don't know how you are calculating your ranking but I ussually use one specific column to rank the values that I need to filter/slice them and them I can use it to have my ranking.

     

    I have created a simple table with Store and Sales then added two measures:

     

    NORMAL_RANK = RANKX(
    		ALLSELECTED(Sales[Store]);
    		CALCULATE(
    			SUM(Sales[Sales])
    			 )
    		    )

    This first measure interacts with the slicers/filter I introduce in the report so it's dinamic and my rank will be influenced by taking out stores from my visual or adding them

     

     

    OVERALL_RANK = RANKX(
    		ALL(Sales[Store]);
    		CALCULATE(
    			SUM(Sales[Sales])
    			 )
    		    )

    In this measure using the ALL function no matter how I have my data sliced it will give me the complete ranking of the store so you can use it in your cards see print below. However using this one if you select more than one value in your slicer in your cards it will give you the first value so be aware of that limitation.

     

     


     

    Regards,

    MFelix

  • wilson_smyth's avatar
    wilson_smyth
    9 years ago

    Apologies, should have responded sooner. 
    Yes, I found  a solution, its pretty much what MFelix suggested.


    I was trying to use one calcuation to do two things:

    - show the rankof all stores

    - show just the rank of the selected score on a card.

     

    The problem was that if i got a calculation to show the list rank, it always displayed a value of 1 when placed on a card, and if i got the correct calculation to show the rank on a card, it wouldnt show the ranked list. so 2 calculations were created.

     

     

    Store Ranking Table = RANKX(ALL('Table'[Store]), CALCULATE([Score]),,0,Dense)

    Individual Store Rank = RANKX(ALL('Table'[Store]), CALCULATE([Score], ALLSELECTED('Table'[Store])),,0,Dense)

14 Replies

  • Hi wilson_smyth,

     

    I don't know how you are calculating your ranking but I ussually use one specific column to rank the values that I need to filter/slice them and them I can use it to have my ranking.

     

    I have created a simple table with Store and Sales then added two measures:

     

    NORMAL_RANK = RANKX(
    		ALLSELECTED(Sales[Store]);
    		CALCULATE(
    			SUM(Sales[Sales])
    			 )
    		    )

    This first measure interacts with the slicers/filter I introduce in the report so it's dinamic and my rank will be influenced by taking out stores from my visual or adding them

     

     

    OVERALL_RANK = RANKX(
    		ALL(Sales[Store]);
    		CALCULATE(
    			SUM(Sales[Sales])
    			 )
    		    )

    In this measure using the ALL function no matter how I have my data sliced it will give me the complete ranking of the store so you can use it in your cards see print below. However using this one if you select more than one value in your slicer in your cards it will give you the first value so be aware of that limitation.

     

     


     

    Regards,

    MFelix

    • Anonymous's avatar
      Anonymous
      Not applicable
      thanks. 
       
      RANKX(ALL(DimProvider),[ActualValue_CRM] )
       
      the above dax formula works fine, but i need the order of the rank to be ASC.
       
      [ActualValue_CRM] is a measure. 
       
       
      • MFelix's avatar
        MFelix
        Super User

        Hi Anonymous ,

         

        The 4rd parameter of the RANKX is the order you just need to do your measure like this:

        RANKX(ALL(DimProvider),[ActualValue_CRM],, ASC )

         

         

    • nardtmo's avatar
      nardtmo
      Helper III

       

       

      Hi Mfelix,

      Thanks in advance. Having issue with ranking ranking starting with 2 after adding row (region). i tried adding isinscope in the formula but ranking is still wrong. thanks

       

      output:  ranking of market selected inside a card in nation and northeast 

      example NY  rank 8 in nation  & in NE rank in 5

       

      RANK =
          if(isinscope(data3[MARKET]),
           RANKX (
           ALLSELECTED(data3[MARKET]),[round_values], , ASC, Dense))
       
       
      overall_rank = RANKX(
                          All(data3[MARKET]),[round_values], ,ASC, Dense)
       
      my data:
       
      MARKETSum of 7Days_Rolling_sale_Per_MarketREGION
      Las Vegas0.3855WEST
      Albuquerque0.3692WEST
      Hawaii0.3565WEST
      San Francisco0.3452WEST
      San Diego0.3434WEST
      Los Angeles0.3341WEST
      LA North0.3305WEST
      Southern California0.3268WEST
      Long Island0.2753NORTHEAST
      Orlando0.2701SOUTH
      Salt Lake City0.2629WEST
      Washington DC0.2627NORTHEAST
      Sacramento0.2614WEST
      New York0.2575NORTHEAST
      Upstate NY0.2453NORTHEAST
      New Jersey0.2407NORTHEAST
      Miami0.239SOUTH
      New England0.238NORTHEAST
      Philadelphia0.2358NORTHEAST
      Virginia0.2356NORTHEAST
      Connecticut0.2329NORTHEAST
      Jacksonville0.2176SOUTH
      Tampa0.2132SOUTH
      South Carolina0.2104NORTHEAST
      North Carolina0.2069NORTHEAST
      Central PA0.1933NORTHEAST

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee

    Hi wilson_smyth,

     

    Have you resolved your problem? Was MFelix's suggestion helpful to your scenario? If yes, please kindly mark the corresponding reply as an answer so that some more people having similar concern can find the solution more easily. If you still have any question, please feel free to ask.

     

    Best regards,
    Yuliana Gu

    • wilson_smyth's avatar
      wilson_smyth
      Post Patron

      Apologies, should have responded sooner. 
      Yes, I found  a solution, its pretty much what MFelix suggested.


      I was trying to use one calcuation to do two things:

      - show the rankof all stores

      - show just the rank of the selected score on a card.

       

      The problem was that if i got a calculation to show the list rank, it always displayed a value of 1 when placed on a card, and if i got the correct calculation to show the rank on a card, it wouldnt show the ranked list. so 2 calculations were created.

       

       

      Store Ranking Table = RANKX(ALL('Table'[Store]), CALCULATE([Score]),,0,Dense)

      Individual Store Rank = RANKX(ALL('Table'[Store]), CALCULATE([Score], ALLSELECTED('Table'[Store])),,0,Dense)

      • wilson_smyth's avatar
        wilson_smyth
        Post Patron

        Quick update - Although the solution worked if using a table and a card, if i try to use either rank measure as the circle size on a , map, they all come out as 1.


        I think theres some filter context im not understanding, but would appreciate any advice.