Announcements
cancel
Showing results for
Did you mean:
Not applicable

## Conditional Format with Hrank - Dynamic Range

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?

1 Solution

Accepted Solutions
MVP

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 Replies
MVP

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(),))

Not applicable
Author

Hi Sunny,

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

MVP

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(),))

Not applicable
Author

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

FYI this worked perfectly

MVP

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