Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only

View My Drafts

  • 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
    ------------------------------

    Original Message


  • 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

    Original Message