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

Gunjan
Gunjan Member
edited February 2024 in Connectors

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

Best Answer

  • ArborRose
    ArborRose Coach
    Answer ✓

    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

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Answers

  • ArborRose
    ArborRose Coach

    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.

    image.png

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • Gunjan
    Gunjan Member

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

    image.png

    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.

    image.png

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

    Best,

    Gunjan

  • DataMaven
    DataMaven Coach

    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"
  • ArborRose
    ArborRose Coach
    Answer ✓

    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

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • Gunjan
    Gunjan Member

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

Categories