Connectors

Connectors

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

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

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In

Best Answer

  • 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

  • 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! **

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

  • 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
    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! **

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

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In