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

Options
Member
edited 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

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%

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

• Coach
Options

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

** Did this solve your problem? Accept it as a solution! **

• Coach
Options

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.

** Did this solve your problem? Accept it as a solution! **

• Member
Options

@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

• Coach
Options

Should the Plan be the series instead?

DataMaven
Breaking Down Silos - Building Bridges
**Say "Thanks" by clicking a reaction in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
• Coach
Options

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

** Did this solve your problem? Accept it as a solution! **

• Member
Options

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