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

[AnnKey]

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

[Ashish_Mathur]

Hi,

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

[Anonymous]

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.

[amitchandak · Accepted solution]

@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]

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

[AnnKey]

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