Hi - I am trying to color coordinate red or green based on whether the sales exceeded the budget.
I had to create a calculated field that identifies the budget for each product by month. When I place the budget and actuals in the view, I want to color them green if actuals are greater than budget and red if actuals are less than budget.
It works for the individual products but when I add the total, the conditional formatting isn't correct. I know it's because the total is comparing to the "average" budget rather than summing everything in the pane but I can't figure out how to get it to do it correctly.
I've attached the workbook and would appreciate any insight into this!
Thank you!
Tori,
As you described, the issue is that the grand total column is not aggregating the way
that is needed. I may not have done it the best way, but one will need to identify
the grand total column in a calc and color it differently.
This may not be necessary, but I used Level of Detail calculations just
so that I could better control the avg and the sum of the avg.
Sales:
{ FIXED [Product Title (group) 1],MONTH([Sale Date]):SUM([Cases])}
Budget:
{ FIXED [Product Title (group) 1],MONTH([Sale Date]):AVG([Budget Cases])}
Skew Color:
IF NOT(ISNULL(MIN([Product Title (group) 1])))
THEN
IF MAX([Check Sales Total])>=MAX([Check Budget Total])
THEN "Sales Exceed Budget"
ELSE "Sales Below Budget"
END
ELSE
IF MAX({ FIXED MONTH([Sale Date]):SUM([Check Budget Total])})>=MAX({ FIXED MONTH([Sale Date]):SUM([Check Budget Total])})
THEN "Sales Exceed Budget"
ELSE "Sales Below Budget"
END
END
Where the else part is for the grand total.
It was seeming to me that because of the group, the Prod Title was null for the grand total.
Please see workbook v2020.1 attached in the Forum Thread:
Conditional Formatting - Color Comparison
