10 Replies Latest reply on Aug 1, 2016 1:51 AM by Ashish Chaudhari

# LOD Calculation error

Hello Everyone,

I am trying to achieve a calculation where I am facing some difficulty. PFA the workbook.

The Calculated field is "Calibration %" which needs attention

The requirement here is I need to find the "count of Blue boxes/count of (Blue+Orange) Boxes."

So as per the report for Accessories it should be 4/7 which is 57.14%

for Appliances it should be 2/5 which is 40%

Regards

• ###### 1. Re: LOD Calculation error

Hi Somok,

Please find the calc and confirm the output by looking at the screenshot.

SUM({ FIXED [Sub-Category] , [Country] : (if SUM([Sales])>1000 then COUNTD([Country]) END) }) /

SUM({ FIXED [Sub-Category],[Country] : COUNTD([Country]) })

Thanks and Regards,

Ashish Chaudhari

2 of 2 people found this helpful
• ###### 2. Re: LOD Calculation error

Hi Ashish,

Many thanks for your response. That's exactly what I was looking for.

Regards,

Somok

• ###### 3. Re: LOD Calculation error

Hi Somak,

You are always welcome. (Thanks for coming up with unique problems all the time. That's what makes community better and better. Keep it up.)

Happy To Contribute to ppl and this community.

-Ashish

• ###### 4. Re: LOD Calculation error

Hi Ashish,

Sorry to reply you lately on this. I forgot to mention the main scenario. I checked the calculation result and that was correct.

Now the calculation which you have given I need to show that beside the Color bars. Something like this below. Currently I am not able to do this on a single worksheet. I have to avoid using it on a dashboard.

• ###### 5. Re: LOD Calculation error

Hi Somak,

I actually tried what you said but I am not able to do so. Please refer to the below screenshot where I am trying to calculated the Sub-Category-wise Country count above Target and trying to divide it by Total countries for that sub category. I am getting wrong count of country which are above target. I am not sure how to do this.

For Eg. Consider 1st subcategory i.e Accessories There are actually 4 Countries are above target and 3 are below target. I get the same result when I go to underlying data I get count as below.

I get 4 countries which are above target but count I get as 3 (Refer countries above target column).

When I tried digging more, by going in to actual raw data. I found below result where i get result as below. (Aus, Austria, AFG are above target)

I am still trying.

-Ashish

1 of 1 people found this helpful
• ###### 6. Re: LOD Calculation error

Hi Somok,

Please find the updated attached workbook. There was a problem with how you were defining the sales target as Sum(sales) >= 1000 which I have fixed. Please make a note of all the calculations.

1. Sales Checker

IF { FIXED [Sub-Category],[Country] : SUM([Sales])} >= 1000

then "Above Target"

else "Below Target"

END

2. Calc_AT_Di_Total (very Stupid name)

{ FIXED [Sub-Category] : COUNTD(if [Sales Checker]="Above Target" THEN ([Country]) END) }

/

{ FIXED [Sub-Category] :COUNTD([Country]) }

Please find the attached result and verify the output that you require.

I hope this one helps.

Thanks and Regards,

Ashish Chaudhari

1 of 1 people found this helpful
• ###### 7. Re: LOD Calculation error

Hi Somak,

I have posted the answer. I am not sure if you can see that post since, I can see a notification/warning that post is being moderated. So pasting calcs here as well.

1. Sales Checker

IF { FIXED [Sub-Category],[Country] : SUM([Sales])} >= 1000

then "Above Target"

else "Below Target"

END

2. Calc_AT_Di_Total (very Stupid name)

{ FIXED [Sub-Category] : COUNTD(if [Sales Checker]="Above Target" THEN ([Country]) END) }

/

{ FIXED [Sub-Category] :COUNTD([Country]) }

Refer below screenshot.

Thanks and Regards,

Ashish Chaudhari

• ###### 8. Re: LOD Calculation error

Hi Ashish,

That's awesome...