Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

How to create dynamic ranking excluding one column the table?

 

I have a summary table as input to Power BI, sample shown below: 

 

Col1Col2Col3Profit
APE546
AQF456
APF343
AQE897

 

I have created a report in Power BI using dynamic ranking. I have used following expression for rank measure:

 

Rank = RANKX(ALLSELECTED(Table), Calculate(sum(Table[Profit])), ,DESC,Dense) 

 

But when filters are applied, above rank is re-generated using all the 3 categorical columns into consideration. But I would like to re-generate ranking based on only 2 columns (suppose col1 and col2 only) when filters are applied and col3 column is only used for filtering purpose. How can I achive it?

 

@lbendin Idrissshatila Ashish_Mathur

Need Help

8 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    Create a slicer for Col3 and select any one item there.  Drag Col1 and Col2 to the Table visual.  Write these measure

    Measure 1 = sum(Table[Profit])

    Measure 2 = rankx(generate(all(Table[col1]),all(Table[col2])),[Measure 1],,DESC,Dense)

    Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks!! There is a little change in requirement. Like, I would like to keep all the 3 columns in the report, hence ranking should be generated based on all 3 columns.

       

      If I apply my initial DAX expression for rank, then I have observed the following:

      1. If there is no filter is applied, ranking are generated correctly.

      2. For certain filter applied, ranking are shown as 1 only for all records.

      3. For other filter(s) applied, ranking are generated correctly.