Display Rank based on overall data not by slicer

[bharathiscripps]

Hi all,

How do I get rank for the selected networks, based on all data (not rank within selected)?

I have over 100 Networks and based on Network Owner slicer, I get 5 networks filtered. I want to rank them on Impression values of all 100 networks.

My current rank function rank those 5 networks within themselves.

OverallNetworkRank = RANKX(ALL(Overnight_Report_Unpivot[Network]), CALCULATE(AVERAGE(Overnight_Report_Unpivot[Impressions])))

[v-luwang-msft]

Hi @bharathiscripps ,

The dax you used need a little adjust(delete the Network),see the below:

rankall = RANKX(all(Overnight_Report_Unpivot),CALCULATE(AVERAGE(Overnight_Report_Unpivot[Impressions])))

And rankall2 is the dax you used:

rankall2 = RANKX(all(Overnight_Report_Unpivot[Network]),CALCULATE(AVERAGE(Overnight_Report_Unpivot[Impressions])))

Final get:

If you sort by your slicer selection，use the below:

OverallNetworkRank2 = RANKX(ALLSELECTED(Overnight_Report_Unpivot),CALCULATE(AVERAGE(Overnight_Report_Unpivot[Impressions])))

This article might help to explain it.

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[MFelix]

Hi @bharathiscripps ,

Looking at your code the information should be based on all the networks and not only on the selected ones.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[bharathiscripps · Accepted solution]

Hi @MFelix ,

Thank you for taking time to help.

I was able to get the desired output by using ALLEXCEPT filter.

RANKX(ALL(Overnight_Report_Unpivot[Network]),

CALCULATE(AVERAGE(Overnight_Report_Unpivot[Impressions]),

ALLEXCEPT(Overnight_Report_Unpivot, Overnight_Report_Unpivot[Telecast Date], Overnight_Report_Unpivot[Demo], Overnight_Report_Unpivot[Network]

)

)

)

Thanks,

Bharathi

[MFelix]

Hi @bharathiscripps ,

This has to do with a context issue, when you add an ALL statement to your formula if you have more columns on your visualizations the logic will return the current line has a "full table", that is why you have the ranking with errors.

Adding the ALLEXCEPT changes the filter context, however if you add some other columns to your visualization that are not included in the EXCEPT you will get the same error.

Don't forget to mark the correct answer to help others.