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

Post a Comment

Popular posts from this blog

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

Image
The entire code shared in this video is designed to automate the process of creating a dynamic line chart based on loan data in the "Loan Report" sheet and display the chart on the "Charts" sheet. The chart shows trends in accumulated principal, accumulated interest, and loan remaining balance over time. The code is also flexible enough to handle both yearly and monthly loan schedule reports. The VBA codes as: Sub CreateLineChart()       '1. Variable Declarations     Dim srcSheet As Worksheet     Dim chtSheet As Worksheet     Dim lastRow As Long     Dim chartObj As Shape     Dim chartDataRange As Range     Dim xAxisRange As Range     Dim yAxisRange As Range     Dim colPrincipal As Variant     Dim colInterest As Variant     Dim colRemainingBalance, ColMonth As Variant     '2. set source and chart sheets     Set srcSheet = ThisWorkbook.Worksheets("Loa...
Read more

Power BI Advanced Visualizations & R : Circular BarPlot with R Script Visual.

Image
  In the video I have shared a method to draw the circular barplot in power BI desktop file. As shown in the screen, the Circular BarPlot, which wraps a barplot that has larger numbers of categorical entities in circular shape, thus with it users can obtain all entities with their measure in a more eye catching organization space chart. To get this Circular Barplot chart in power BI we need to employ the power BI R script visual.  To Enable R scripts Visual feature in Power BI Desktop , we need go to File > Options and settings > Options > R Scripting. In the R scripts options you have two fields to fill. First one is to choose home directory for R, which means you need first to install a R engine and select the installed R engine location for the field. To install R engine we may click the  link ”how to install R”. it brings us to the Microsoft learning site regarding where to download R engine and how to install it.  The second field is to choose which R I...
Read more

Excel Loan Calculator with functions PMT, IPMT, PPMT and it’s generated Report via VBA Macro .

Image
  Loan calculations involve using mathematical formulas to determine important loan aspects such as monthly payments, total interest, and overall cost. They are essential for financial planning and widely used by individuals, businesses, and financial institutions. Excel provides powerful loan calculation functions like PMT, PPMT, and IPMT, enabling users to analyze loans effectively. In my Excel example, these functions are utilized to create a basic Loan Calculator, where users input loan details and obtain calculated results for monthly and final repayments. Additionally, Excel's VBA codes can automate the generation of long-term loan reports, providing comprehensive information on each payment period. Enabling the Developer tab and accessing VBA procedures allows users to streamline and expedite loan analysis. My Excel VBA codes can automate the generation of long-term loan reports: Sub GenerateLoanReport_monthlyPeriodReport()          ' Created by MIKE...
Read more