8 Replies Latest reply on Feb 18, 2016 12:53 PM by Patrick A Van Der Hyde

# Grand total not working properly.

Hi I have one scenario ..

My data looks like this :

When doing a count distinct of salesman data looks like this :

But what I actually want is SUM([Sales])/TOTAL(COUNTD([Sales man])) i.e Sales per number of person working in that region.

But when I apply this formula everything comes correct except sum of Grand total.

I have referred to these blogs:

http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/

They were not of much help.

It will be great if someone suggests something.

PFA tableau worksheet.

Thanks

Anubhav

• ###### 1. Re: Grand total not working properly.

Hello Anubhav Saxena,

The total of 7 is correct for the grand totals of Total Countd of Salesmen because there are 7 unique salesmen when looking across all days ( the way this calculation is defined in your example workbook).    By changing the Table Calculation to utilize a Table Down Calculation for the Total we get the "Correct" total amount per Day of Sales divided by Salesmen.

See attached workbook with the different pages to show how this was worked out.

patrick

• ###### 2. Re: Grand total not working properly.

Hi Patrick

thanks for your reply..

I guess I have not explained my question properly.

My scenario is:

screenshot 2:

For January 1st 2015 value should come as 1100/3 = 366.66 (for location UP) and in grand total same value should reflect.

For 2nd January value will be 2200/4 = 550 (for location MP) and 550 should reflect in grand total.

and so on

Thanks Anubhav

• ###### 3. Re: Grand total not working properly.

Then lets do this the easy way and use LOD calcs.

Sum(Sales)/ Sum( {Fixed [Location]: Countd([Sales man])})  - updated with aggregate Sum() for the LOD calc.

1 of 1 people found this helpful
• ###### 4. Re: Grand total not working properly.

awesome....... thanks a lot Patrick

• ###### 5. Re: Grand total not working properly.

But Patrick,

If suppose 1jan 2015 location - AP is having some value  and in 1jan 2015 location:UP is having value 367.

Will grand total calculation work for this type of data.

• ###### 6. Re: Grand total not working properly.

What would you like for the total in that scenerio?

Let's take and move the AP numbers from the 4th to the first.

Now we get 4500 and 1100 for sales for the 1st.  with 2 and 3 salesmen respectively.   For 5600/5 for a total of 1120.  This is what you will get as-is.

You could also change the way totals are calculated and select the Agg(Totals with LOD) field on the marks shelf and select "Totals Using" from the bottom of the drop down menu - change this to "Sum" and you will get the Sum of 2250 and 367 - or 2617..  This gives you a sum of the two sales per Location per Date.

Does that get the desired outcome?

Patrick

• ###### 7. Re: Grand total not working properly.

Yeahhhhhh that's what I want ..... awesomeeee thanks a lot

• ###### 8. Re: Grand total not working properly.

Anubhav,

Glad to be of help and thank you for visiting the Tableau Community

Patrick