Forum Discussion

bharathiscripps's avatar
bharathiscripps
Frequent Visitor
5 years ago
Solved

Display Rank based on overall data not by slicer

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 wan...
Need Help
  • bharathiscripps's avatar
    bharathiscripps
    5 years ago

    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
bharathiscripps's avatar
bharathiscripps
Frequent Visitor
5 years ago

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's avatar
MFelix
Icon for Super User rankSuper User
5 years ago

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.