Max of 3 records and filter the max

[akj2784]

Hi All,

I have a requirement to pick the max of % for 3 records and filter the same record.

For the below set of records, I have to pick the 8.33% record. So the report should show only 3rd record.

Please let me know if there is any way to do this. 

[v-yulgu-msft]

Hi @akj2784,

If [Metric2/Metric1*100] is a column, please create a rank value with below measure:

Rank =
RANKX (
    ALLSELECTED ( Sheet1 ),
    CALCULATE ( SUM ( Sheet1[Metric2/Metric1 *100] ) ),
    ,
    DESC,
    DENSE
)

While if [Metric2/Metric1*100] is a measure, please modify the rank measure as below:

Rank =
RANKX (
    ALLSELECTED ( Sheet1 ),
   Sheet1[Metric2/Metric1 *100] ,
    DESC,
    DENSE
)

Add the [Rank] measure to visual level filter, and set its value to 1.

Best regards,

Yuliana Gu

[akj2784]

Thank you. I will give it a try and let you know.

[akj2784]

Rank =
RANKX (
    ALLSELECTED ( Sheet1 ),
   Sheet1[Metric2/Metric1 *100] ,
    DESC,
    DENSE
)

This gives error. But when put the same column in the value part of the syntax it assigns the same rank for all records.

I assume sheet1 would be the table which has the % column on which we want to calculate rank.

I think Allselected part is not working somehow for me. If I remove that and just gives the table name it ranks all the data. 

[akj2784]

Would you be able to pass on the sample pbix file ?

Also is it working fine if we filter on rank as 1 ? 

[v-yulgu-msft]

Hi @akj2784,

I have uploaded the .pbix file for your reference.

Regards,

Yuliana Gu

[akj2784]

This works fine in your file. But we have metric coming from different table and there are 4 other tables involved in this. So when we put ALLSELECTED(TABLE which has metric), it splits the records in lower grain and doesnt rank properly.

[v-yulgu-msft]

Hi @akj2784,

Could you please illustrate your scenario with more information?

Regards,

Yuliana Gu