Percent of total line chart

user060355

Is there an easy way to graph percent of total on a line chart rather than the individual values?

It seems like it shouldn't be too difficult but I don't see an option.

amehdad

Yes there is, in your chart, go to Chart Properties -> Data Label Settings -> Click the plus sign on Text to reveal a list -> Click on %_PERCENT_OF_TOTAL

user060355

That simply changes the label. It does not appear to have an impact on the values in the chart. As you can see in the image, the scale on the left is not percent of total.

Screen Shot 2022-01-21 at 8.48.15 AM.png

MarkSnodgrass

You need to make sure the "window functions in beast modes" is enabled in your instance. Your CSM can get it enabled for you if it is not. You can then do something like this, changing the name of the field to yours:

sum(amount) / sum(sum(amount)) 

 

 You can watch this video for a good walkthrough about it.

https://www.youtube.com/watch?v=eifSYZIcPzg&t=868s

user060355

@MarkSnodgrass if I am graphing count(id) over time, how would I change your formula?

MarkSnodgrass

Dealing with counts is a little different. Try this:

SUM(SUM(1)) OVER (PARTITION BY `id`) / SUM(SUM(1)) OVER ()

user060355

Hmm... Doesn't seem to be working. It seems bizarre that this is difficult.

GrantSmith

SUM(1) / SUM(SUM(1)) OVER ()

SUM(1) will get you the total number of within the grouping defined on your card.

SUM(SUM(1)) OVER ()

This returns the total for your entire dataset after filtering has been applied.

This version is simply counting the number of rows for each group and the total number of rows across the entire dataset. If you care about just counting IDs (if it may be null sometimes) you can do something like:

COUNT(`id`) / SUM(COUNT(`id`)) OVER ()