Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Add dynamic ranking column that updates when a slicer is selected

    Posted Dec 14, 2022 04:33 PM
    I am trying to build a dynamic rank column that will update when a slicer is selected. There are 2 slicers on the page: RegionalManager and SalesManager
    There is only one table say tblSales. I have tried various combinations of RANKX but nothing seems to work. Can someone help me with this? Here is a sample data and scenarios.

    RegionalManager SalesManager    SalesPerson Sales   Rank
    Bill             Patty          John         20      6
    Bill             Patty          Sally        10      7
    Bill             Patty          Connie       30      4
    Bill             Connie         Jim          40      3
    Bill             Connie         Amanda       70      1
    Zack             Tracy          Trevor        5      8
    Zack             Matt           Breanna      25      5
    Zack             Mike           Pam          45      2
    
    If I filter on Bill the Rank should be this:
    RegionalManager SalesManager    SalesPerson Sales   Rank
    Bill             Patty          John         20      4
    Bill             Patty          Sally        10      5
    Bill             Patty          Connie       30      3
    Bill             Connie         Jim          40      2
    Bill             Connie         Amanda       70      1
    
    If I filter on Bill and Connie the Rank should be this:
    RegionalManager SalesManager    SalesPerson Sales   Rank
    Bill             Connie         Jim          40      2
    Bill             Connie         Amanda       70      1


    ------------------------------
    Mike McLean
    ------------------------------


  • 2.  RE: Add dynamic ranking column that updates when a slicer is selected
    Best Answer

    Posted Dec 14, 2022 05:06 PM
    Veja se ajuda

    Medida = 
    RANKX (
        FILTER ( ALLSELECTED( Tabela ), [RegionalManager] = MAX ( Tabela[RegionalManager] ) ),
        CALCULATE ( SUM ( Tabela[Sales] ) ),
        ,
        DESC
    )​


    ------------------------------
    Vilmar Santos
    ------------------------------



  • 3.  RE: Add dynamic ranking column that updates when a slicer is selected

    Posted Dec 15, 2022 08:49 AM
    Edited by Mike McLean Dec 15, 2022 10:56 AM