I'd like to color code the percentages in the 2nd column based on their change from the corresponding row in the 1st column. (e.g., 0% in Q3+Q4 would be red because it's less than the value in the 1st column. Any values that went up would be green.) Demo TWBX attached.
Thanks!
I have a simpler solution (well, at least with fewest calculated fields...)
1) create a calculated field called Share of ED level; it must compute the same result than the % you're displaying, but with LOD instead of Table Calc.
COUNT([Extract]) / ATTR({EXCLUDE [ED Level] : COUNT([Extract])})2) create a second calculated field called Delta Growth, which will be a nullable boolean : in Q1+Q2, the result is NULL ; in Q3+Q4, the result will be True if the share is bigger and False if it's smaller :
IF ATTR([FY Qtr (group)]) = "Q1+Q2" THEN NULL
ELSE [Share of ED level] >= LOOKUP([Share of ED level], -1)
END
3) set the Default Table Calc to FY Qtr (group), so the Lookup(..., -1) will get the value from the previous column.
4) use this field on Color, so you can set NULL to black, True to green and False to Red.
---
Antoine Dinimant
Freelance consultant, trainer & teacher
