Can I sum 2columns one being Aggregation SUM and other (No Aggregation) fixed number?

IgneP
IgneP Member
in Beast Mode

Good day!

I am trying to add a column with a count of a TOTAL SUM of different values in Transpose table. (simply: Rename Fee+
(amFee-Platinum amFee)+
Platinum amFee+
VIP No IVA+
Collector price no IVA) etc

My columns that needs to be Summed are in format: 123=SUM() Aggregation SUM and one column is a fixed number in format 123=Expenses (No Aggregation). this later column is causing me a lot of trouble haha, as in my Total SUM count at the end it shows up as a huge number (probably a sum of smht) instead of the fixed one.

This is my 123=Expenses (No Aggregation) CASE

CASE

WHEN nmOrganizer= 'Hub Music Factory S.r.l.' AND nmPerformance= 'The Offspring' THEN -2294.62
WHEN nmOrganizer= 'Hub Music Factory S.r.l.' AND nmPerformance= 'Social Distortion' THEN -964.54

ELSE 0 END

Here is the

123=SUM() Aggregation example

amRenameFee/1.22

Is this happening because it's not possible to sum Aggregation SUM with (No Aggregation) fixed number? or it's smth else?

hope it makes sense, if NOT I can give more details.

Thank you in advance!

Best Answers

  • DavidChurchman
    DavidChurchman Coach
    edited February 27 Answer ✓

    I'm not 100% I understand what you're saying, so I'm going to say what I think you're saying, and then you can correct me if I missed something. (GrantSmith's suggestion is a good one, and likely to lead to a faster/better answer.)

    I think your data looks something like this:

    image.png

    And then you have BeastModes like this:

    image.png

    and:

    image.png

    In a table card, you put a "Sum" on the Aggregation beastmode and "No aggregation" on the Expenses. You added a total row and transposed and got something like this:

    image.png

    The Aggregation column (transposed to a row) gives you what you expect, but the Expenses does not. That's because, even though you told it not to aggregate, when you added a Total row, you contradicted yourself and you told it to aggregate after all. Not knowing which function you wanted, it defaulted to a SUM, so instead of counting the "-2294.62" expense once for "the Offspring", it counted it for every row where "the Offspring" appeared.

    To fix this, you need to tell your BeastMode for Expenses how you want to aggregate the column. ColemanWilson was correct that you'll want to use a FIXED function. Based on what I assume about your data, it would probably look like this:

    image.png

    You average it by nmOrganizer & nmPerfomance, which has the effect of using one value for all the rows where the organizer and performance are the same (instead of repeated values), and then sums across those averages.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    Since you have mixed types of aggregation, I think instead of putting the SUM on the drop-down of the Total beastmode, where it affects the entire BeastMode, I would write it into the BeastMode itself:

    SUM(

    Online Fee +

    (amFee-`Platinum amFee`)+

    …

    Royalty Inside Charge) +

    AVG( `Ticketing Royalties Expense 1 `)

    The thing is that your Ticketing Royalties Expense 1 BeastMode assigns a value of $-2,294.62 to every row of your data where the Organizer is HubMusicFactory and the Performer is The Offspring. So every row where that is true gets added to your total. Averaging it across the group acts as simplifying it into a single value.

    One note of caution is that your current set-up won't work if you have multiple nmPerformers in your data. You either need to insure a single performer is filtered or include the performer as a column in your table.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited February 27

    You can use a FIXED function: https://domo-support.domo.com/s/article/4408174643607?language=en_US

    If I solved your problem, please select "yes" above

  • IgneP
    IgneP Member

    thank you, but sorry I do not understand how to use this in my case

  • GrantSmith
    GrantSmith Coach

    Can you provide a sample/anonymized data table of how your data is structured to help us better understand your use case?

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

    I'm not 100% I understand what you're saying, so I'm going to say what I think you're saying, and then you can correct me if I missed something. (GrantSmith's suggestion is a good one, and likely to lead to a faster/better answer.)

    I think your data looks something like this:

    image.png

    And then you have BeastModes like this:

    image.png

    and:

    image.png

    In a table card, you put a "Sum" on the Aggregation beastmode and "No aggregation" on the Expenses. You added a total row and transposed and got something like this:

    image.png

    The Aggregation column (transposed to a row) gives you what you expect, but the Expenses does not. That's because, even though you told it not to aggregate, when you added a Total row, you contradicted yourself and you told it to aggregate after all. Not knowing which function you wanted, it defaulted to a SUM, so instead of counting the "-2294.62" expense once for "the Offspring", it counted it for every row where "the Offspring" appeared.

    To fix this, you need to tell your BeastMode for Expenses how you want to aggregate the column. ColemanWilson was correct that you'll want to use a FIXED function. Based on what I assume about your data, it would probably look like this:

    image.png

    You average it by nmOrganizer & nmPerfomance, which has the effect of using one value for all the rows where the organizer and performance are the same (instead of repeated values), and then sums across those averages.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • IgneP
    IgneP Member

    thank you !!!!

    DavidChurchman, almost correct, only that at the end I want to SUM Aggregation with Expense as per example in the picture. So I was creating an additional column:

    Total

    Aggregation + Expenses

    example.PNG
  • IgneP
    IgneP Member

    Only that in real life example I want to SUM more than 2 values (not like in the case above Aggregation plus Expense). It was just a simplification. But it does not change much I guess.

    image.png

    Here is Ticketing Royalties expense No aggregation CASE

    image.png

    And here is a TEST Total SUM

    image.png

    and this 'Ticketing Royalties Expense 1' - is giving me a wrong Total.

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    Since you have mixed types of aggregation, I think instead of putting the SUM on the drop-down of the Total beastmode, where it affects the entire BeastMode, I would write it into the BeastMode itself:

    SUM(

    Online Fee +

    (amFee-`Platinum amFee`)+

    …

    Royalty Inside Charge) +

    AVG( `Ticketing Royalties Expense 1 `)

    The thing is that your Ticketing Royalties Expense 1 BeastMode assigns a value of $-2,294.62 to every row of your data where the Organizer is HubMusicFactory and the Performer is The Offspring. So every row where that is true gets added to your total. Averaging it across the group acts as simplifying it into a single value.

    One note of caution is that your current set-up won't work if you have multiple nmPerformers in your data. You either need to insure a single performer is filtered or include the performer as a column in your table.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • IgneP
    IgneP Member

    Thank you so much, that was super helpful!!!

    AVG function was the solution!

Categories