5 Replies Latest reply: Sep 7, 2017 12:21 PM by Sunny Talwar RSS

    Conditional Format with Hrank - Dynamic Range

    Connor Smith

      Hi All,

       

      I am currently working on building a P&L pivot table for multiple locations (the reason I am using Hrank). However, I am using slicers, so the maximum ranking is constantly changing based on my selections. For simplicity, lets assume that my selection gives me 8 locations. I am trying to highlight the top 3 a color and the bottom 3 a color, but am having a difficult time with creating a dynamic rank for the end ranking (i.e 8)

       

      I know that this equation works:

      If(HRank(total(sum(MS_FGS_Sales_Labor) -  sum(MS_FGS_COGS_Labor)) / sum(MS_FGS_Sales_Labor)) <=3, RGB(102,178,255),

      If(HRank(total(sum(MS_FGS_Sales_Labor) - sum(MS_FGS_COGS_Labor)) / sum(MS_FGS_Sales_Labor)) >5, LightRed(),))


      But this equation does not:

      If(HRank(total(sum(MS_FGS_Sales_Labor) -  sum(MS_FGS_COGS_Labor)) / sum(MS_FGS_Sales_Labor)) <=3, RGB(102,178,255),

      If(HRank(total(sum(MS_FGS_Sales_Labor) - sum(MS_FGS_COGS_Labor)) / sum(MS_FGS_Sales_Labor)) >(Count($(dim_StoreNumber)) - 3), LightRed(),))


      I have tested the (Count($(dim_StoreNumber)) - 3) = 5 by plugging the information into a simple KPI. However, when I use it as a range, the equation highlights all of the values above the top 3. Can you provide explanation as to why the second equation does not work and how to correct it?