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

IgneP · 2025-02-27T09:12:32+00:00

There was an error rendering this rich post.

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!

Find more posts tagged with

Accepted answers

DavidChurchman

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:

And then you have BeastModes like this:

and:

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:

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:

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.

DavidChurchman

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.

All comments

ColemenWilson

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

IgneP

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

GrantSmith

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

DavidChurchman

I think your data looks something like this:

And then you have BeastModes like this:

and:

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:

IgneP

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

IgneP

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.

Here is Ticketing Royalties expense No aggregation CASE

And here is a TEST Total SUM

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

DavidChurchman

SUM(

Online Fee +

(amFee-`Platinum amFee`)+

…

Royalty Inside Charge) +

AVG( `Ticketing Royalties Expense 1 `)

IgneP

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

AVG function was the solution!