4 Replies Latest reply on Jan 19, 2019 11:04 AM by Stephen Groff

# How to represent "ALL" in a calculation.

I am having a **** of a time trying to figure out how to get my targets to align with my charts.

I have 5 different products.

Each product has it's own target goal for each month.

The goals show up well based on the product family chosen.

However, when I choose "ALL" under my product filter... the targets don't reflect as they should

Spelling it out... If Product Family = ALL then give me the Facility Target, if it gives me something other than ALL, then give me the TARGET

(The targets are in 2 different tables. One table is the facility goals join on date, not joined to a product family, the other table is the product family goals, and those are joined on product family and date)

Looking at the RAW data, the target it is showing is from the PDI_TARGET measure... it doesn't bring over the FAC_PDI_TARGET.

Any idea what I need to do to get this to work properly?  Many thanks!

• ###### 1. Re: How to represent "ALL" in a calculation.

It is more likely how your tables are joined rather than analysis thing. Any chance of attaching Wbook?

• ###### 2. Re: How to represent "ALL" in a calculation.

See attached workbook

• ###### 3. Re: How to represent "ALL" in a calculation.

I think this will do the trick ...

I created this calculation

[Target (FAC or Product)]

IF COUNTD([Product Family]) = SUM({COUNTD([Product Family])})

THEN MIN([FAC_PDI_TARGET])

ELSE MIN([PDI_TARGET])

END

which returns the FAC TARGET if the COUNTD of Products in the View is the same as the Global total of Products (i.e. ALL selected), or Returns the PDI TARGET otherwise.

I've added this to your Sheet1 so you can see what it does.

Hope that helps, and makes sense. Let me know if not

1 of 1 people found this helpful
• ###### 4. Re: How to represent "ALL" in a calculation.

Awesome job.  Never even crossed my mind.  Thank you for your help!

1 of 1 people found this helpful