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

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'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.