8 Replies Latest reply: Jan 2, 2017 6:49 AM by EUGENIO FERNANDEZ ORTIZ

# Calculation filtering some  values of a dimension

Hello:

I have a dimension called "Centro" (Values: ND1, ND2, ND3, ND4, NR1, NR2, NR3, NR4, NF1, NF).

I want to calculate the measure "%Scrap" as "Sum(CHTQuantity)/Sum(PQuantity)*100" but only with some values from the dimensión "Centro" (NR1,NR2,NR3, NR4, NF1 and NF). How can define the formula?

• ###### Re: Calculation filtering some  values of a dimension

Hi,

use set analysis

Sum({<Centro = {'NR1', ''NR2', ...}>} CHTQuantity)

BR,

Matus

• ###### Re: Calculation filtering some  values of a dimension

Thank you Matus

• ###### Re: Calculation filtering some  values of a dimension

Hi Eugenio,

Can you share sample data with expected to do the same.

Regards,

Arun Kumar A

• ###### Re: Calculation filtering some  values of a dimension

As suggested by others,

Use the measure as

Sum({<Centro = {'NR1','NR2','NR3', 'NR4', 'NF1',' NF'}>}CHTQuantity)

/

Sum({<Centro = {'NR1','NR2','NR3', 'NR4', 'NF1',' NF'}>}PQuantity)*100

or you can also write a calculated dimension as

if(Match(Centro,'NR1','NR2','NR3', 'NR4', 'NF1',' NF'), Centro)

and your expression as it is.

Dont forget to check " Supress when value in Null' in Dimensions tab for Centro

• ###### Re: Calculation filtering some  values of a dimension

The slide shows several graphics regarding full factory but in case to the ratio %Scrap must be shows only the agregate value for specific Production centers.
The list of production center is over 100 and for scrap purposes should be arround 45.
Depending user selection if not specific centers are selected , ratio must be "Blank" . That´s working but if I select all the specific centers and after I deselect some of theirs, the value does not changed.
I am able to show the "%scrap" value but not to do it variable as a rest of KPI´s on the slide.

For example I am using the following formula:
Sum({<Centro = {'NR1','NR2','NR3', 'NR4', 'NF1',' NF'}>}CHTQuantity)/
Sum({<Centro = {'NR1','NR2','NR3', 'NR4', 'NF1',' NF'}>}PQuantity)*100

If I deselect NR1 and RR2 the value does not change.
How can I do?

Do you need more detail to help me?
Thank you

• ###### Re: Calculation filtering some  values of a dimension

What is the filter you are selecting, are you selecting "Centro", if so, then the expression doesn't change as you have restricted the expression for some values but if you select other fields which are associated with Centro then the expression value should change.

• ###### Re: Calculation filtering some  values of a dimension

Thank you Kumar:

I am using this formula and Now is working.

Sum({\$<Centro={"NR1","NR2","NR3","NR4","NF1","NF"}>}CHTQuantity)/Sum({\$<Centro={"NR1","NR2","NR3","NR4","NF1","NF"}>}PQuantity)*100