Percentage of values in a column

agolla2
agolla2 Member
in Beast Mode

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

Tagged:

Best Answer

  • david_cunningham
    david_cunningham Coach
    edited May 7 Answer ✓

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

    SUM(SUM(value)) over ()
    image.png

    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
    image.png

    Which can then be used to generate your desired output.

    image.png

    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.

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

Answers

  • david_cunningham
    david_cunningham Coach
    edited May 7 Answer ✓

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

    SUM(SUM(value)) over ()
    image.png

    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
    image.png

    Which can then be used to generate your desired output.

    image.png

    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.

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • agolla2
    agolla2 Member

    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.

Categories