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

IgneP

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!

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.