How to create dynamic ranking excluding one column the table?

[Anonymous]

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

Col1	Col2	Col3	Profit
A	P	E	546
A	Q	F	456
A	P	F	343
A	Q	E	897

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

[Padycosmos]

Hope this helps

[Anonymous]

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.

[Padycosmos]

I hope this helps

[Anonymous]

Now I understand. currently, we don't want to explicitly add slicer into the report but I have added filters in Filters pane. Is there a way for the ranking to work with filters in Filters pane? Is the slicer mandatory in the report page for ranking?

[Padycosmos]

You can use the same Measure with filters, slicers are not necessary

[Padycosmos]

Ok, Shall see

[Anonymous]

Thanks & Apologies!! 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.

[Ashish_Mathur]

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.