Dividing Two Grouped Columns

nmizzell
nmizzell Contributor
in Magic ETL
image.png

How do I divide two columns that are grouped? The Revenue Column is taking the sum of all revenue for each D9-THC bucket. The same applies to the Quantity Sold Column. However, the Days column takes the distinct count of days where these products were sold. I would like to create a new column "Revenue Per day" that divides revenue by the days column. How can I do this while retaining the ability to filter by date, and have all my values and the newly calculated field change?

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓

    @nmizzell A beast mode calculation would be your best bet since the calculation will update based on whatever filters are selected. The formula should look something like this:

    sum(`Revenue`)/count(distinct `Date`)

Answers

  • GrantSmith
    GrantSmith Coach

    You can use a beast mode for this

    SUM(`Revenue`) / SUM(`Days`)

    You can use the same aggregation and logic for the revenue and days

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MichelleH
    MichelleH Coach
    Answer ✓

    @nmizzell A beast mode calculation would be your best bet since the calculation will update based on whatever filters are selected. The formula should look something like this:

    sum(`Revenue`)/count(distinct `Date`)
  • nmizzell
    nmizzell Contributor

    Thank you! This worked.

Categories