Pivot Table Coloring

JasonKerr

I have a very simple question that is proving very tricky to work through.

Given the following Pivot Table, I am just trying to highlight the "Actual" based on whether or not they hit their "Goal".

Basic Color rules have not been working since you can't color a column based on another column. "Apply to Full Row" does not appear to work on Pivot Tables either. Nor is HTML supported on Pivot Tables.

This is an incredibly simple request, I'm surprised it's given me this much trouble to begin with, but there has to be a way to accomplish this, right?

DavidChurchman

If the data is long in the way I structured my example (one column has both actual values and goal values), then not really. You could create a separate card with monthly totals, and include the same symbol in the data labels. (I recommend a bullet chart to show meeting the goal visually by month).

If the data is wide, as I suspect yours is (one column for Actual, one column for Goal), then you should be able to get the symbol in your total row.

case when sum(Actual)>=sum(Goal) then concat(sum(Actual),'✅') else concat(sum(Actual),'❌') end

ArborRose

You could create a beast mode calculation to determine whether each actual value meets the corresponding goal.

CASE 

   WHEN `Actual` >= `Goal` THEN 'Goal Met'

   ELSE 'Goal Not Met'

END

Add to your table. Apply conditional formatting with this status.

JasonKerr

@ArborRose As stated in the question, this does not solve my problem.

"Apply to Full Row" does not appear to work on Pivot Tables.

DavidChurchman

Yeah, I would love an out-of-the-box solution for this.

Some possible options.

1. Collapse into one column, "Actual - Goal" and color code that instead:

sum(case when Measure= 'Actual' then Value end) Fixed(by ID, Month) - sum(case when Measure= 'Goal' then Value end) fixed(by ID, Month)

2. Concatenate an icon to your value to show the goal has been met or not met:

case
when Measure = 'Goal' then sum(Value)
when Measure = 'Actual' and sum(case when Measure= 'Actual' then Value end) fixed(by ID, Month) >= sum(case when Measure= 'Goal' then Value end) fixed(by ID, Month)
then concat(sum(Value), '✅' )
else concat(sum(Value), '❌')
end

I'm realizing you probably don't have your data in this longer structure with goals/actuals in the same column, so you probably won't need to use "fixed" in your beastmodes, but you get the idea.

JasonKerr

@DavidChurchman Thanks for the suggestion! Unfortunately, the first one is not going to work for the stakeholders, but option b is a nice workaround in lieu of being able to color code the column. (I hadn't realized you could use symbols in a table)

It would be nice if Domo could make coloring like this available in Pivot Tables though, this seems like a really simple thing to be able to fix/implement!!

JasonKerr

@DavidChurchman Sorry, quick follow-up here. Is there anyway to get the Total Row to show the total while still utilizing this solution? Because the CONCAT function changes the data type to character, it is not currently summing the total correctly to display at the bottom. Any workaround there?

DavidChurchman

If the data is long in the way I structured my example (one column has both actual values and goal values), then not really. You could create a separate card with monthly totals, and include the same symbol in the data labels. (I recommend a bullet chart to show meeting the goal visually by month).

If the data is wide, as I suspect yours is (one column for Actual, one column for Goal), then you should be able to get the symbol in your total row.

case when sum(Actual)>=sum(Goal) then concat(sum(Actual),'✅') else concat(sum(Actual),'❌') end

JasonKerr

@DavidChurchman You're correct about the structure of my data the with one slight qualification, which I think is causing the error here. My data is structured so that the "Actuals" and "Goal" are in different columns, however, the Goal is consistent for every observation, whereas the Actuals need to be summed.

Ex)

ID Actual Goal

A 10 40

B 45 130

C 15 90

B 30 130

B 100 130

C 60 90

A 15 40

As a result, the Goal column is a beast mode in the table:

AVG( Goal ) OVER (PARTITION BY ID, MONTH(Date))

So my ending beast mode looks more like:

case when sum(Actual)>= (AVG( Goal ) OVER (PARTITION BY ID, MONTH(Date))) then

concat(sum(Actual),'✅')

else concat(sum(Actual),'❌')

end

This beast mode yields a blank value in the Total row (see attached picture).

Btw, thank you for your help on this!

DavidChurchman

I think the case statement is going to have a hard time when the level of aggregation is different for the different clauses. Maybe I'm still not fully understanding your data, but do you really need the partition? If the goal is just repeating every time, then just a straight average would still work, right?

case when sum(Actual)>= AVG( Goal ) then

concat(sum(Actual),'✅')

else concat(sum(Actual),'❌')

end