Conditional Format with Hrank - Dynamic Range

[#1]

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 😎

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?

[sunny_talwar]

How about this?

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(TOTAL $(dim_StoreNumber)) - 3), LightRed(),))

[#3]

Hi Sunny,

Unfortunately that erased the top 3 highlights and did not highlight anything red (all background colors remain unchanged).

[sunny_talwar · Accepted solution]

I might have accidentally removed a parenthesis, can you try again

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(TOTAL $(dim_StoreNumber)) - 3), LightRed(),))

[#5]

Perfect! Can you explain the reasoning of having to add TOTAL vs just having the count there?

FYI this worked perfectly

[sunny_talwar]

From your response, it seemed that you had Location as your dimension and when you did just count(), you probably were getting 1 for each location.... by using Total, it showed 8 (sum of all location) across all location on the chart.... So 1-3 = -2, but you needed 8-3 for this to work