1 2 Previous Next 16 Replies Latest reply on Sep 8, 2017 9:49 AM by Minnu Meena

# Grand Total Issue

Hello All,

I have created a formula:

After creating the formula i got correct values but grand totals are coming wrong.

But the total was wrong. I should get 4.78 instead of 4.97.

When i am selecting total using sum  then i got  correct total.

How can i get correct  grand total automatically. I have to use this total value in another calculation. So how i can i achieve this?

Thanks & Regards,

Minnu.

• ###### 1. Re: Grand Total Issue

You can use Window_SUM , if you need to use it in other calculations.

• ###### 2. Re: Grand Total Issue

If i am applying Window_Sum, calculation it self getting wrong value so how i use this into another cal. ?

• ###### 3. Re: Grand Total Issue

Hello,

Pooja Gandhi

Simon Runc

Regards,

Minnu.

• ###### 4. Re: Grand Total Issue

hi Minnu,

So using a WINDOW_SUM (with Category in the VizLoD) on your calculation, and then running the WINDOW_SUM by Category should give you the right result, but you'd need to have Category in the VizLoD (viz Level of Detail) whenever you used it. You can probably create a FIXED LoD, which would allow you to calculate this "Total" number "off canvas" (meaning that the result will be independent of your VizLoD)

How we'd build the FIXED LoD will depend on the Level of Detail the calculation is run at. So although you've got Category in your current view, I can see you have a filter on a single SOL_ID, so if you want this calculated by SOL_ID/Category you'd need to include both in the FIXED LoD.

It's hard to be exact without seeing a workbook, are you able to post an anonymised version? (it can just be a small subset of the current data, with at least 2+ SOL_IDs, and any other dimensions that are needed in the grain of this calculation). I'll then be happy to take a look.

• ###### 5. Re: Grand Total Issue

Hello Simon,

Thank you so much for responding.

Actually my issue was not getting correct total when i am applying the sol_id filter.

Normal calculated field giving correct value. when ever applying a category or ant dimension then it is giving wrong value. I have applied windows_sum,fixed aswell but still it was giving wrong value. I need corrcet value because i have to use that cal field total as reference line as month level. so the reference line value as well giving wrong value.

Even I have tried with the current dummy data there it was giving correct total but when applying in main dashboard level values giving wrong.

I am attaching the workbook with the relevant data. Kindly Plz go through it.

Thanks & Regards,

Minnu.

• ###### 6. Re: Grand Total Issue

Thanks for the workbook...

So is the 43.66 value correct here?

And if so do you want this to be the same for every category and every SOL_ID

Currently if I filter to SOL_ID = 003 for example I get a different value

• ###### 7. Re: Grand Total Issue

Yeah for now in that data grand totals coming correct only. Any how its a dummy data.

Thank yo so much Simon for working. Any way i will try to get a data related to this query by tomorrow. Plz help me  if possible.

• ###### 8. Re: Grand Total Issue

Hopefully last question....

So is the 43.7 (well 43.66) figure correct because it's the MIN of the 3 SOL_IDs we have in this (dummy) data? It's not that only SOL_ID = 2 that determines the correct value?

• ###### 9. Re: Grand Total Issue

No Actually in my data i have more than 2000 SOLS.

So after applying the filter the actual total and getting total differeence was like

eg: My actual total should be :43.35

Me getting total was :  43.60 .

The difference  was nearly 30 to 40 . So in this way for every filter giving me wrong value.

• ###### 10. Re: Grand Total Issue

So in this (dummy) example you've said the correct value is 43.66...this number is only returned by your calculation for SOL_ID = 002. I'm trying to determine why SOL_ID = 002 gives the correct value. If it is that a particular SOL_ID (in this case 002) gives the right result, we can encode this rule and return the 43.66 to any SOL_ID (and so in your real world example, if there is a particular SOL_ID that gives the correct result, you can just change my formula to use that one instead), but if SOL_ID = 002 gives the correct result because (of the 3 examples in the dummy data) this one is the lowest value, I can use that rule instead. I've (of course) no idea what SOL_IDs are!!

• ###### 11. Re: Grand Total Issue

Hello Simon,

Here i have attached the workbook with the exact data.

Hope now you can understand my problem.

When i am applying the filter then the values were getting wrong. Plz help me.

Thanks & Regards,

Minnu.

• ###### 12. Re: Grand Total Issue

Just to check...

So the value in the Grand Total here...1.161 is the correct one we are aiming for?

If so, what is the reason that it's correct when SOL_ID = 20? If it's just because that SOL_ID (whatever a SOL_ID is!) 20 is "special" in someway, that's fine (I can encode the formula so it always returns the Grand Total value for SOL_ID=20 to all SOL_IDs), but what to check there isn't a deeper reason.

• ###### 13. Re: Grand Total Issue

When sol id =20 grand total getting 6.13. This was wrong, actual value is 6.00

The same way sol id =60  total getting was 4.97 but actual should get was 4.79.

If we are selecting individual values after applying sol filter category level the total was correct. After applying grand total it is showing wrong.

For eg: If SOL ID=8

Electricity=1.11

Postage=0.93

Printing stationery=1.20

Repair and maintenance = 5.80

Travelling= 3.41

If normally we are adding these 1.11+0.93+1.20+5.80+3.41=12.44 is correct.

But in the grand total we are getting 12.60. This is not correct

Like this for every SOL_ID value was giving wrong.

So i am assuming in this way.

Actually SOL_ID its nothing but Branch ID'S.

• ###### 14. Re: Grand Total Issue

OK I think I see....

I've used this LoD...

[Revised Monthly Limit - LoD]

{FIXED [Sol Id]: SUM({FIXED [Sol Id], [Category]: [Revised Monthly Limit]})}

and now I get the numbers you want, without needing Category in the VizLoD (or changing how the Grand Totals compute)

so this works out the  [Revised Monthly Limit] at Sol_Id/Category Level, and does a SUM of these values by Sol_Id

Hope that does the trick

1 2 Previous Next