Pivot Table Coloring
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?
Best Answer
-
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
),'❌') endPlease 💡/💖/👍/😊 this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
0
Answers
-
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'
ENDAdd to your table. Apply conditional formatting with this status.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **-1 -
@ArborRose As stated in the question, this does not solve my problem.
"Apply to Full Row" does not appear to work on Pivot Tables.
0 -
Yeah, I would love an out-of-the-box solution for this.
Some possible options.
- Collapse into one column, "Actual - Goal" and color code that instead:
sum(case when
Measure
= 'Actual' thenValue
end) Fixed(byID
,Month
) - sum(case whenMeasure
= 'Goal' thenValue
end) fixed(byID
,Month
)2. Concatenate an icon to your value to show the goal has been met or not met:
case
whenMeasure
= 'Goal' then sum(Value
)
whenMeasure
= 'Actual' and sum(case whenMeasure
= 'Actual' thenValue
end) fixed(byID
,Month
) >= sum(case whenMeasure
= 'Goal' thenValue
end) fixed(byID
,Month
)
then concat(sum(Value
), '✅' )
else concat(sum(Value
), '❌')
endI'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.
Please 💡/💖/👍/😊 this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
0 -
@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!!
0 -
@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?
0 -
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
),'❌') endPlease 💡/💖/👍/😊 this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
0 -
@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 BYID
, MONTH(Date
))So my ending beast mode looks more like:
case when sum(
Actual
)>= (AVG(Goal
) OVER (PARTITION BYID
, MONTH(Date
))) thenconcat(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!
0 -
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
) thenconcat(sum(
Actual
),'✅')else concat(sum(
Actual
),'❌')end
Please 💡/💖/👍/😊 this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 126 Manage
- 123 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive