Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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(),))
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(),))
Hi Sunny,
Unfortunately that erased the top 3 highlights and did not highlight anything red (all background colors remain unchanged).
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(),))
Perfect! Can you explain the reasoning of having to add TOTAL vs just having the count there?
FYI this worked perfectly
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