4 Replies Latest reply on Sep 12, 2017 11:27 AM by jacob.buehler.1

# SumIf other dimension values do not exist

Hello,

I have a data set containing a list of material numbers that fall into multiple buckets but always into a "GEN" one as well.  I am trying to find a way to sum the amount of materials that fall into the "GEN" bucket only and no others.

For example:

Material 1234 can be in bucket A, B, GEN, D

Material 1111 falls into bucket GEN only

The data set is composed of multiple lines with a Dimension differentiating the buckets.  How can I do a SUM of materials that fall into the "GEN" bucket ONLY and no others?   Where the Sum below would be 2 and not 4.

Material     Bucket

1234               A

1234               B

1234               GEN

1234               D

1111               GEN

4444               A

4444               GEN

5555               GEN

Thanks,

Jacob

• ###### 1. Re: SumIf other dimension values do not exist

Hello Jacob,

Not the most elegant solution, but maybe it will work for you?

Using source with the values you provided above:

Calculation 1: IF [Bucket] = 'GEN' THEN 1 ELSE -1 END

Calculation 2: { FIXED [Material] : IF SUM([Calculation 1]) > 0 THEN 1 END }

Result of Calculation 2 should return a value of 2 for the bucket 'GEN'

best,

Mike

3 of 3 people found this helpful
• ###### 2. Re: SumIf other dimension values do not exist

Hi Jacob,

Below is an alternative solution which you can do in one step or two steps as you may wish. See attached workbook in version 10.0

Step 1: Create calculated field [GEN Only]

IF  {FIXED [Material]: COUNTD([Bucket])} = 1 AND {FIXED [Material], [Bucket]: MIN([Bucket])} = "GEN" THEN [Material] END

Step 2: Create calculated field [# of Materials in GEN Only]

COUNTD([GEN Only])

You can skip this calculation and simply drag [GEN Only] to the Text Marks and select CNTD().

Hope this helps.

Ossai

1 of 1 people found this helpful
• ###### 3. Re: SumIf other dimension values do not exist

Hi Michael,

Thanks for this solution.  Yes not ideal but got the job done.  Great work!

Jacob

• ###### 4. Re: SumIf other dimension values do not exist

Hi Okechukwu,

Thanks for this solution.  This works and does allow for only one calculated field.  Both your's and Michael's solutions resulted in the same outcome.

Thank you both!

Jacob