3 Replies Latest reply on Feb 23, 2018 8:07 AM by swati.jiandani

Count-Calculated field

Hello all,

I have a report which shows product name in column. This has 3 products A,B,C.

I want to create a column calculated field-X- where the condition should be as:

among A, B and C number of products having value >100% should be the count in field X.

For example if A>100%, B & C <100% Then X=1

or A <100, B & C>100% then X=2 and if all 3 >100% Then 3 and if all 3 <100% Then 0.

How can i achieve this? What formula can I use to get this calculated field?

Swati

• 1. Re: Count-Calculated field

Hi,

Try this in a calculated field:

IF {INCLUDE [Product Name]:(SUM([Value]))} > 100 THEN

1

ELSE

0

END

• 2. Re: Count-Calculated field

Thanks Sunil!

But this formula is not similar to the condition I am looking for.

I will need to highlight those rows depending on if it's 1 then yellow, 2 green 0 gray and so on

• 3. Re: Count-Calculated field

I need something like this:

Region || Territory ||             Product                         Calculated field

Prd-X   ||   Prd-Y || Prd-Z

East        NY             100.4%     97%     123%                  2

NJ              92.6%      84%     45%%                0

FL               100%       95.6%    94%                 1

There is a filter on Team. So if I select team1 there are 3 relevant products and if team 2 there can be 4 relevant  products and so on. It doesn't go beyond 4 products for each team. I need a way to get the calculated field column which shows if 2 among 3 products are more than 100% then display 2 and if just one is more then 100% display 1 and none display 0 and if all 3 display 3. Basically its the count of products more than 100% but what would be the calculation to achieve this?