Partial Totals and Level Aware Calculation

Question & Answer
1

I have the following data:

UUID Amount Project ID
a1 1 A
a1 1 A
a2 2 A
a3 3 A
b1 3 B
b2 2 B

I want to calculate the total amount for each project while getting rid of duplicates based on UUID.
Here is the result.

UUID Amount Project ID Project Total
a1 1 A 6
a1 1 A 6
a2 2 A 6
a3 3 A 6
b1 3 B 5
b2 2 B 5

I was thinking about something like this but it doesn’t work. Any help would be much appreciated!

sumOver( min(Amount,[UUID]), Project ID)

2

Is this how you want your visual to look?

It’s much easier if you create your visual like this instead:

Project ID Project Total
A 6
B 5

Project Total = sum(min(Amount, [{Project ID}, UUID]))

3

No, I need it to look like the table I posted. Your solution wouldn’t even get rid of the duplicate rows.

4

Sorry, UUDI was missing in my calculated field. It should be:
Project Total = sum(min(Amount, [{Project ID}, UUID]))

If you want your visual to be like you showed, try this:

Project Total = 
sumOver(
	Amount / countOver(UUID, [UUID], PRE_AGG),
	[{Project ID}],
	PRE_AGG
)
1 Like