I want to create a column that contains the total sum of another column.

Hi everyone, looking to create a column that contains total revenue in order to find individual contributions. I have tried to do this using rank/window function within the magic ETL however all I get is a running total.

Any help appreciated :) Thanks!

Tagged:

Best Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @kav_100 I'd suggest using a Group by Tile to find the totals, then joining that back to your detail data

  • Ashleigh
    Ashleigh Coach
    Answer ✓

    @kav_100 you can also try to use fixed functions in a beast mode, its like doing a group by but in a card so you keep all your granularity.

    https://domo-support.domo.com/s/article/4408174643607?language=en_US

    **If this answer solved your problem be sure to like it and accept it as a solution!

  • ST_-Superman-_
    Answer ✓

    The Group by tile will work if you want to perform this calculation in ETL. However, the downside there is that the total will be calculated based on the logic that you build into the ETL. For example, if you partition your totals to give you the total monthly sales you would not be able to change the data in a visualization to show weekly sales. Also, if you wanted to exclude a particular product or region, you would need to determine that prior to the group by tile and would not be able to change that decision at the card level.

    I would suggest using a calculated field and a variable for this. Something like:

    Variable 1:

    Create a variable that would allow you to select the metric that you want to partition the data by. Either Month, Year, Region, Manager, etc.

    Then create a beastmode to use that variable:

    SUM(SUM(`Sales`)) FIXED (BY `Variable1`)

    This would allow more flexibility in terms of how you are totaling the sales. Also, by using a calculated field, if you decide to filter out a product or region, The calculation will respond to those filters and update the value in your card.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @kav_100 I'd suggest using a Group by Tile to find the totals, then joining that back to your detail data

  • Ashleigh
    Ashleigh Coach
    Answer ✓

    @kav_100 you can also try to use fixed functions in a beast mode, its like doing a group by but in a card so you keep all your granularity.

    https://domo-support.domo.com/s/article/4408174643607?language=en_US

    **If this answer solved your problem be sure to like it and accept it as a solution!

  • ST_-Superman-_
    Answer ✓

    The Group by tile will work if you want to perform this calculation in ETL. However, the downside there is that the total will be calculated based on the logic that you build into the ETL. For example, if you partition your totals to give you the total monthly sales you would not be able to change the data in a visualization to show weekly sales. Also, if you wanted to exclude a particular product or region, you would need to determine that prior to the group by tile and would not be able to change that decision at the card level.

    I would suggest using a calculated field and a variable for this. Something like:

    Variable 1:

    Create a variable that would allow you to select the metric that you want to partition the data by. Either Month, Year, Region, Manager, etc.

    Then create a beastmode to use that variable:

    SUM(SUM(`Sales`)) FIXED (BY `Variable1`)

    This would allow more flexibility in terms of how you are totaling the sales. Also, by using a calculated field, if you decide to filter out a product or region, The calculation will respond to those filters and update the value in your card.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman