7 Replies Latest reply on Mar 20, 2018 8:20 AM by Michael Ye

# Excluding Item from Totals

Greetings again -

Can't seem to figure out how to do this:

I have a basic data set that looks like this:

Company     # of Sales     \$\$ Sales

A                         10               5000

B                         20               7500

C                         42               9000

D                         30               6500

I want to be able to get the column totals w/all Companies included, which is easy enough,

But I also want to get the Totals while excluding 1 or more members - say I want column Totals for # of Sales and for \$\$ Sales, but excluding Company C.

How do I do that?  What does that calculation look like?

Thanks for any help.

Brian.

• ###### 1. Re: Excluding Item from Totals

Hi Brian,

Please see the screenshot. Make two calculations which exclude C:

IF FIRST()!=LAST()

THEN SUM([# of Sales])

ELSE SUM(IF [Company]!='C' THEN [# of Sales] END)

END

IF FIRST()!=LAST()

THEN SUM([\$\$ Sales])

ELSE SUM(IF [Company]!='C' THEN [\$\$ Sales] END)

END

The totals will exclude C.

Hope it helps.

Michael Ye

• ###### 2. Re: Excluding Item from Totals

Thank you very much for that help.  It gets me down the road some, but the problem is w/my example.

Forgive me, I’m new to all of this, including writing the calculations.

What we do is evaluate freight pricing.  So I have data that looks like:

Shipment           Company           Rate

1                           A                          200

1                           B                          100

1                           C                          175

1                           D                          185

1                           E                           225

2                           A                          450

2                           B                          250

2                           D                          700

2                           E                           375

2                           F                           400

So I might have 5 different companies who will run these shipments, but not necessarily the same 5.  But all I want to do is add up the Lowest prices on each shipment – once w/all the companies included and once w/out Company B, for example.

So in the above data, I just want to return Totals of \$350 (Totaling the lowest prices on each shipment w/all companies included), and then another Total showing the sum of the lowest prices on each shipment, but Excluding Company B = \$550.

I apologize for the lack of specificity in my earlier example.

Any further help appreciated.

Thanks

Brian.

• ###### 3. Re: Excluding Item from Totals

Brian,

Please see the screenshot.

make a calculated field: Total for Smallest rate for each Shipment:

IF FIRST()!=LAST()

THEN SUM([Rate])

ELSE SUM({ FIXED [Shipment]:MIN([Rate])})

END

This will add rate for company B in shipment 1 and 2, totally 350 (100+250).

Make another calculation:

Other totals:

IF FIRST()!=LAST() THEN SUM([Rate])

ELSE (TOTAL(SUM([Rate]))-SUM({FIXED [Shipment]:MIN([Rate])}))

END

This is the total rate exclude that of company B in each shipment.

Thanks,

Michael Ye

• ###### 4. Re: Excluding Item from Totals

I’m sorry – I don’t see any differences in the 2 calculations below?

Brian K. Boddy

SGI, LLC

Analytics

509.222.3043

• ###### 5. Re: Excluding Item from Totals

Revised.

First one:

IF FIRST()!=LAST()

THEN SUM([Rate])

ELSE SUM({ FIXED [Shipment]:MIN([Rate])})

END

second one:

IF FIRST()!=LAST() THEN SUM([Rate])

ELSE (TOTAL(SUM([Rate]))-SUM({FIXED [Shipment]:MIN([Rate])}))

END

Michael Ye

• ###### 6. Re: Excluding Item from Totals

Thank you!

Brian K. Boddy

SGI, LLC

Analytics

509.222.3043

• ###### 7. Re: Excluding Item from Totals

Brian,

If it is what you need, please mark it as correct and close the thread so others can make it as references. Thanks,

Michael Ye