I want to Rank this Matrix table in a descending manner based on the last month with value

[Dyke211]

Hi guys, I have a Power BI challenge. I want to rank a Matrix table based on the last month column that contains a value. This means that months with zeros should not be considered for sorting, but instead the most recent month with a non-zero value (in this case, July).

How can I achieve this? Please help.

[v-dineshya]

Hi @Dyke211 ,

Thank you for reaching out to the Microsoft Community Forum.

You  want to rank a Matrix table based on the last month column that contains a value., means that months with zeros should not be considered for sorting, but instead the most recent month with a non-zero value (in this case, July).

Please refer below output snap and attached PBIX file.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[Dyke211]

Thanks for this, looks close but in a case where I want to sort by month in July based on rank but dont want rank column like (Rankby LastNonZero) to show in the table . WHat should show is just the Months but its should be sorted by july 25

[v-dineshya · Accepted solution]

Hi @Dyke211 ,

You don't want to show the (Rankby LastNonZero) in the table. And want to show  just the Months but its should be sorted by july 25.

Solution: Drag the "RankByLastNonZero" measure into the Values field temporarily .

Click the Matrix visual  --> open the visual header menu (three dots ...)  --> Sort by --> choose "RankByLastNonZero"  and set Descending. After the rows are sorted correctly, remove "RankByLastNonZero" from the Values. The visual remembers the sort and will stay sorted by that measure.

While "RankByLastNonZero" field in values  , refer snap.

while sorting the measure in descending order, refer snap.

Please refer below output result before removing "RankByLastNonZero" field in Values.

Please refer below output result , After removing "RankByLastNonZero" field in Values.

Note: Their is no change in final output after removing "RankByLastNonZero" field in Values. Now you can't see the "RankByLastNonZero" in Matrix visual.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-menakakota]

Hi @Dyke211  ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Best Regards, 
Community Support Team 

[v-dineshya]

Hi @Dyke211 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @Dyke211 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[Hoangechip910]

hi

First need to define which month is ranked

+Need a extra index column (1,2,3,4,...) correcsond to Months Order, you will filter which months have SUM (values) > 0, then find Max of Index Number.

Second do Ranking

Filter your table which month you found in first, then rank, use function RANKX.

[Dyke211]

DId not work