4 Replies Latest reply on Feb 14, 2020 7:03 AM by sri.reddy.2

    Top N on multiple dimensions issue


      I have a requirement where i has to do dynamic top n on all the dimensions displayed for sales.


      For ex: Region, state, city to be filtered  on top n.


      1. created a calculation  -- RANK_DENSE(SUM([Sales])) and applied it city and filtered by top n/3

      2. to get top n by state, i tried options like index and alternative way using lod as well.

          created LOD with exclude and added to filter -----  RANK_DENSE(sum({EXCLUDE [City]:sum([Sales])}))

          the issue here is,  when ever i change the top n for cities, based on the sum of values it is not changing the top n for states.

      Ex: in the image below, i applied top 2 sales by city and the rank for Massachusetts should come as 2 but it is not changing with respect to the option chosen.



      I tried with index, but didn't work, can you please let me know if there are any other options that will work.