Calculation filtering some values of a dimension

[#1]

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?

Thank you in advance

[mkelemen · Accepted solution]

Hi,

use set analysis

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

You can find more info here A Primer on Set Analysis

BR,

  Matus

[#3]

Hi Eugenio,

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

Regards,

Arun Kumar A

[kkkumar82]

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

[#5]

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

[#6]

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.

[#7]

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

[#8]

Super , close this thread , by making a thread answer as correct , so that it will be helpful to others.

[#9]

Thank you Matus