Power BI DAX Formula for Dynamic Ranking Calculations Based on User Selections and Filters

 

The Youtube Vidoe: Power BI DAX: Dynamic Ranking Calculations Based on User Selections and Filters        



DAX formulas from the Video:


Total Opp count = DISTINCTCOUNTNOBLANK('Fact'[Opportunity ID])

Total P_Revenue = SUM('Fact'[ProductRevenue])

Total F_P_Revenue = SUM('Fact'[FactoredProductRevenue])


Selected KPIs = SELECTEDVALUE('Table_KPIs'[KPI Name])


Account value rank index = 

    SWITCH(

        TRUE(),

        [Selected KPIs]="Opportunity Numbers",RANKX(ALL('Account'[Account]),[Total Opp count],,,Dense),

        [Selected KPIs]="Product Revenues",RANKX(ALL('Account'[Account]),[Total P_Revenue],,,Dense),

        [Selected KPIs]="Factored Product Revenues",RANKX(ALL('Account'[Account]),[Total F_P_Revenue],,,Dense)

    )


Account KPI values for sorting = 

    SWITCH(

        TRUE(),

        [Selected KPIs]="Opportunity Numbers",[Total Opp count],

        [Selected KPIs]="Product Revenues",[Total P_Revenue],

        [Selected KPIs]="Factored Product Revenues",[Total F_P_Revenue]

    )


Comments

Popular posts from this blog

Power BI DAX and Visualizations: Active employees across time (as x-axis) by Tenure group or age group

Excel VBA Tips: A Macro to Generate Line Chart Dynamically for Loan Schedule Trend Report