5 Replies Latest reply on Mar 6, 2019 4:53 AM by Sindhu Ayyadapu

# SUM on GROUP

Good Afternoon,

Given the following data set:

LocationChargeIDTransIDAmount
MainA488T95656\$80.00
MainA488T24586\$80.00
Main65BvT84457\$76.00
East5G51T96367\$90.00
East5G51T47852\$90.00
East5G51T12214\$90.00

My goal is to get it down to:

Location
Amount
Main\$156.00
East\$90.00

I can do this all day in SQL but am trying to do it in the Tableau UI. I've tried the suggestions below but it never gives the right sum (way too high).

How to calculate sum of distinct values

I can't post my twbx because it contains confidential data but can try to dummy it up if need be.

• ###### 1. Re: SUM on GROUP

David,

Will this work for you?

1 of 1 people found this helpful
• ###### 2. Re: SUM on GROUP

Hi David,

Use this formula. Refer to the screenshot. Also Attaching the tableau workbook (Version 10) for your reference.

SUM({ INCLUDE  [Charge ID]: AVG([Amount])})

Thanks and Regards,

Ashish Chaudhari

• ###### 3. Re: SUM on GROUP

Hi Sherzodbek,

I guess no LOD formulas can be same ever. This is really nice. I have followed bit different approach.

-Ashish

• ###### 4. Re: SUM on GROUP

Both worked, thanks!

• ###### 5. Re: SUM on GROUP

Hi Ashish,

I have similar requirement SUM on GROUP, apart from that I need to filter a condition on this. I want something like

IF [Transport Mode Gid (group)]='AIR' THEN SUM({ INCLUDE  [Shipment Gid]: AVG([Total Shipment gross weight])}) END

I was getting correct value for SUM({ INCLUDE  [Shipment Gid]: AVG([Total Shipment gross weight])}) but when I give a IF condition I getting an error, I want SUM of Total shipment gross weight for different modes like 'AIR', 'GROUND' and 'OCEAN'

could you please let me know how to achieve this.

Thank you very much

Regards,

Sindhu