Line chart to show the values as % of the column total

Gunjan · February 26

I need to create a line chart in Domo with my existing dataset. I have the idea in excel pivot table but I need some help in getting the same in Domo line chart.

As an example, my pivot table is this data:

Count of ID	Column Labels
Row Labels	Plan A	Plan B	Plan C	blank	Grand Total
good	89	4	29	260	382
bad	7	2	5	106	120
evil	325	35	113	1632	2105
Grand Total	421	41	147	1998	2607

I changed it to show value as % of column total

Count of ID	Column Labels
Row Labels	Plan A	Plan B	Plan C	blank	Grand Total
good	21%	10%	20%	13%	15%
bad	2%	5%	3%	5%	5%
evil	77%	85%	77%	82%	81%
Grand Total	100%	100%	100%	100%	100%

How do I represent this in a line chart in Domo where x-axis is for good, bad, evil and y-axis is value as % of column total for Plan A, B, C and blank as shown above.

Thanks,

Gunjan

ArborRose · February 28

Hmm….sorry my workload is heavy today.

As DataMaven mentioned…I'm not sure what I'm looking at with your fields in x and y. As for the %…there might be an issue in how your calculate the percentage of the total. You can try to calculate the % of a column total separately for each plan and combine them. Something like this might give the % relative to the total for the plan.

CASE
WHEN `Code` = 'A' THEN `Plan A` / SUM(`Plan A`) OVER ()
WHEN `Code` = 'B' THEN `Plan B` / SUM(`Plan B`) OVER ()
WHEN `Code` = 'C' THEN `Plan C` / SUM(`Plan C`) OVER ()
END

To get the % of column total relative to the total of all….

CASE
WHEN `Code` = 'A' THEN `Plan A` / (SUM(`Plan A`) OVER () + SUM(`Plan B`) OVER () + SUM(`Plan C`) OVER ())
WHEN `Code` = 'B' THEN `Plan B` / (SUM(`Plan A`) OVER () + SUM(`Plan B`) OVER () + SUM(`Plan C`) OVER ())
WHEN `Code` = 'C' THEN `Plan C` / (SUM(`Plan A`) OVER () + SUM(`Plan B`) OVER () + SUM(`Plan C`) OVER ())
END

ArborRose · February 26

Change the values from % to decimal. For example, instead of 21%, use the value .21 so Domo sees it as a measurement.

Create a card and choose the "Line Chart" option.

For the X-axis (categories), you'll want to select the Row Labels (good, bad, evil).
For the Y-axis (values), you'll want to select each of the Plan columns (Plan A, Plan B, Plan C, blank).

Make sure that the aggregation function for the Y-axis is set to "Average" or "Sum" depending on your preference.

Gunjan · February 27

@ArborRose hi,

Thank you so much. This is helpful. I actually created fake data for this example in excel. My actual dataset that I will be using in Domo doesn't really have a field that identifies plans. It has codes for plans. So, I created a beast mode calculation that would get me the plans. I built something like this for all plans individually, and it works. example for plan A, the beast mode was:

CASE WHEN `Code= 'A' THEN `Plan A`

END

Then, for X-axis I got my rating good, bad, ugly

for Y-axis - Here's the problem since it's a beast mode calc and not a measure, I can only put one plan on the y-axis. I put Plan A and the numbers are correct. They show up as % of column total. I have my data label setting as %_PERCENT_OF_TOTAL

for series - I created another beast mode calc to get plan names

Here's how my card looks like for Plan A measure only, which is half the battle won. I need the line chart to get me lines for all the plans and show as % of totals.

So, I created another beast mode to get all my plans in one calc: It looks like this

CASE WHEN `Code= 'A' THEN `Plan A`

ENDWHEN `Code` = 'B' THEN `Plan B`

WHEN `Code` = 'C' THEN `Plan C`

END

When I bring this up on the Y-axis I see line for all plans YAY!, but my numbers are incorrect for % of column total.

Would you be able to advice on what am I doing wrong here. Much appreciated and thanks in advance.

Best,

Gunjan

DataMaven · February 28

Should the Plan be the series instead?

ArborRose · February 28

Hmm….sorry my workload is heavy today.

As DataMaven mentioned…I'm not sure what I'm looking at with your fields in x and y. As for the %…there might be an issue in how your calculate the percentage of the total. You can try to calculate the % of a column total separately for each plan and combine them. Something like this might give the % relative to the total for the plan.

CASE
WHEN `Code` = 'A' THEN `Plan A` / SUM(`Plan A`) OVER ()
WHEN `Code` = 'B' THEN `Plan B` / SUM(`Plan B`) OVER ()
WHEN `Code` = 'C' THEN `Plan C` / SUM(`Plan C`) OVER ()
END

To get the % of column total relative to the total of all….

CASE
WHEN `Code` = 'A' THEN `Plan A` / (SUM(`Plan A`) OVER () + SUM(`Plan B`) OVER () + SUM(`Plan C`) OVER ())
WHEN `Code` = 'B' THEN `Plan B` / (SUM(`Plan A`) OVER () + SUM(`Plan B`) OVER () + SUM(`Plan C`) OVER ())
WHEN `Code` = 'C' THEN `Plan C` / (SUM(`Plan A`) OVER () + SUM(`Plan B`) OVER () + SUM(`Plan C`) OVER ())
END

Gunjan · February 28

@ArborRose thanks again.

X - axis is rating for good, bad, ugly

Y- axis is a beast mode calc for all plan:

CASE WHEN `Code'= 'A' THEN `Plan A`

WHEN `Code` = 'B' THEN `Plan B`

WHEN `Code` = 'C' THEN `Plan C`

END

Series is a beast mode for Plan type

The issue is when I say my Y-axis is just this beast mode:

Case WHEN 'Code'= 'A' THEN 'Plan A'
END

I get value as % of column. When I change it to the beast mode calc for all plan it is throwing me off.

I'll try doing what you mentioned and see what I get.

Line chart to show the values as % of the column total

Best Answer

Answers

Categories