Calculation of a row value by the Grand total for that selection

user020184
user020184 Member
edited March 2023 in Datasets

Hi!

I have a situation where I need to do a calculation of Allocated amount which is based on a formula like this: Hours of the employee for a particular Project divided by the grand total of hours for that employee for any selected date range and this multiplied by the rate of employment. I am not able to get the Grand Total aligned dynamically against each row so I can use it for my calculation. Basically I have a table like below:


How do I achieve this either in Beast mode or MYSql if its possible, I am not able to get the 56 total to use on my formula.


Help will be greatly appreciated.


Thanks

Answers

  • What you're describing is a window function. If you abstract your problem and google "percent of total" SQL, you'll find loads of solutions.

    You'll have to ask your CSM or support to enable "Window functions in Analyzer" but after that

    sum(sum(hours_worked)) over () will give you the 56

    https://www.youtube.com/watch?v=ZPf41Fjn1H8&t=10s

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Thanks so very much , that resolved my issue.


    Thanks