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

Gunjan

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

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

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

@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

Should the Plan be the series instead?

ArborRose

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

@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.