Percentage of values in a column

agolla2 · 2024-05-07T19:00:05+00:00

There was an error rendering this rich post.

Hello,

In the beast mode how should I get the percentage of column totals like below, 50 hours worked is the 12 percent of the total hours (410). I need to create a column like % Hours so that I can make further calculated fields based upon that.

Provider	Hours worked	Total Tickets	% Hours	% Tickets
John	50	20	0.12	0.10
David	60	30	0.15	0.15
Mark	100	50	0.24	0.25
Antony	200	100	0.49	0.50

Find more posts tagged with

Beast Mode

Accepted answers

david_cunningham

@agolla2 you can use a window function to achieve this.

SUM(SUM(value)) over ()

Then you can use that as an input in another beast mode (you could also combine these all into 1 beast mode). Best to separate it out if you will be using it in other calculations.

SUM(value)/total_hours

Which can then be used to generate your desired output.

A couple things to keep in mind. This window function calculates the total over all data. If you need to split by group, you'll need to make use of the PARTITION BY argument.

You can use the same approach to calculate % of tickets.

All comments

david_cunningham

@agolla2 you can use a window function to achieve this.

SUM(SUM(value)) over ()

Then you can use that as an input in another beast mode (you could also combine these all into 1 beast mode). Best to separate it out if you will be using it in other calculations.

SUM(value)/total_hours

Which can then be used to generate your desired output.

A couple things to keep in mind. This window function calculates the total over all data. If you need to split by group, you'll need to make use of the PARTITION BY argument.

You can use the same approach to calculate % of tickets.

agolla2

Hey David! Thanks for sharing it worked for me.

But I'm trying to use these beast mode calculations to do a weighted average like we do in excel.

Example of single value weighted average for 2 columns: "=SUMPRODUCT(F48:F70,E48:E70)/SUM(E48:E70)"

My question is can we still try to do the weighted average even after using the window function calculation we did. Because we might face an aggregation issue as in, we cannot refer an already referred beast mode calculation. I hope you got my question.