
1. Re: LOD Calculation error
Ashish Chaudhari Jul 26, 2016 2:41 AM (in response to Somok Sarkar)Hi Somok,
Please find the calc and confirm the output by looking at the screenshot.
SUM({ FIXED [SubCategory] , [Country] : (if SUM([Sales])>1000 then COUNTD([Country]) END) }) /
SUM({ FIXED [SubCategory],[Country] : COUNTD([Country]) })
Thanks and Regards,
Ashish Chaudhari

Calculation.twbx 1.0 MB


2. Re: LOD Calculation error
Somok Sarkar Jul 26, 2016 3:35 AM (in response to Ashish Chaudhari)Hi Ashish,
Many thanks for your response. That's exactly what I was looking for.
Regards,
Somok

3. Re: LOD Calculation error
Ashish Chaudhari Jul 26, 2016 3:45 AM (in response to Somok Sarkar)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
Somok Sarkar Jul 28, 2016 11:58 PM (in response to Ashish Chaudhari)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
Ashish Chaudhari Jul 29, 2016 2:03 AM (in response to Somok Sarkar)1 of 1 people found this helpfulHi 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 SubCategorywise 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

6. Re: LOD Calculation error
Ashish Chaudhari Jul 29, 2016 5:55 PM (in response to Somok Sarkar)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 [SubCategory],[Country] : SUM([Sales])} >= 1000
then "Above Target"
else "Below Target"
END
2. Calc_AT_Di_Total (very Stupid name)
{ FIXED [SubCategory] : COUNTD(if [Sales Checker]="Above Target" THEN ([Country]) END) }
/
{ FIXED [SubCategory] :COUNTD([Country]) }
Please find the attached result and verify the output that you require.
I hope this one helps.
Thanks and Regards,
Ashish Chaudhari

Calculation.twbx 1.0 MB


7. Re: LOD Calculation error
Ashish Chaudhari Jul 29, 2016 5:58 PM (in response to Ashish Chaudhari)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 [SubCategory],[Country] : SUM([Sales])} >= 1000
then "Above Target"
else "Below Target"
END
2. Calc_AT_Di_Total (very Stupid name)
{ FIXED [SubCategory] : COUNTD(if [Sales Checker]="Above Target" THEN ([Country]) END) }
/
{ FIXED [SubCategory] :COUNTD([Country]) }
Refer below screenshot.
Thanks and Regards,
Ashish Chaudhari

8. Re: LOD Calculation error
Somok Sarkar Aug 1, 2016 1:14 AM (in response to Ashish Chaudhari)Hi Ashish,
That's awesome...
Many Thanks for your efforts.

9. Re: LOD Calculation error
Ashish Chaudhari Aug 1, 2016 1:51 AM (in response to Somok Sarkar)You are welcome.
Ashish