Forum Discussion

AnnKey's avatar
AnnKey
New Member
2 years ago
Solved

dynamic Ranking, in a Model with two fact tables and a calculated measures table

Hi everyone,

I really need some help with my ranking. I have two fact tables with each offering the base KPI, a calculated Measures Table where I tried to do that dynamic Ranking and other KPI Measures...

Tried allready a lot of different approaches but no way of solving that Ranking...

 

Base KPIs: 

fact_TableA[Contracts Amount]

fact_TableB[Customer Amount]


Measured KPIs:
Contracts_actual = CALCULATE(SUM[Contracts Amount]), Filter actual)
Customer_actual = CALCULATE(SUM[Customer Amount]), Filter actual)

Contract_ratio = DIVIDE(AVERAGE([Contracts_actual]),(AVERAGE[Customer_actual]))

 

Dimension Stores with [Store ID]

 

How do I know create a dynamic Ranking for the Contract_ratio so that there is a Ranking for the Stores?

I guess it will need to create calc.Columns instead of measures???

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    AnnKey ,

    The measure should be

    Divide([Contracts_actual], [Customer_actual])

     

    Rank can be

     

    Rankx(allselected(Table[Stores]), [Contract_ratio])

     

    Measure Rank: https://www.youtube.com/watch?v=DZb_6j6WuZ0&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=40
    https://www.youtube.com/watch?v=cN8AO3_vmlY&t=25627s

     

    Power BI Rank Across dimension tables: https://youtu.be/X59qp5gfQoA

    Power BI - New DAX Function: RANK - How It Differs from RANKX: https://youtu.be/TjGkF44VtDo

     

    • AnnKey's avatar
      AnnKey
      New Member
      2 years ago

      The RANKX Measure just gives me "1" for every Store. Alltough the ratio is different vor each.

       

    • AnnKey's avatar
      AnnKey
      New Member
      2 years ago

      Well I hat to clean my Data Model. Now it works.  Thank you

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi AnnKey ,

    To create a dynamic ranking for the Contract_ratio, you can use the RANKX function in a measure. The RANKX function returns the rank of an expression evaluated in the current context within a specified scope. You can use the ALLSELECTED function to specify the scope as the Stores dimension table, and use the Contract_ratio measure as the expression. For example, you can create a measure like this:

    Rank by Contract_ratio = RANKX(ALLSELECTED(Stores[Store ID]), [Contract_ratio])

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Share the download link of the PBI file and show the expected result there very clearly.