10 Replies Latest reply on Oct 5, 2016 1:11 AM by Heinz Meier

# Wrong SUM Calculation in Profit

Hi there,

I'm consued abaout a little calculation - the single values are ok - but the Sum over one area (here a customer) ist wrong.

what can I Do to fix this ?

Regards,

Heinz

• ###### 1. Re: Wrong SUM Calculation in Profit

Hi Heinz,

You need to amend the way you are aggregating the Costs_Total measure. Instead of summing the individual measures and then multiplying, sum after the multiplication:

Costs_Total:

sum([Cost EA] * [Units Sold])

You should get the below:

Is this what you were looking for?

Thanks

Ben

• ###### 2. Re: Wrong SUM Calculation in Profit

Hi Ben,

thank's - yes in this simple example it works. In my original file I'm having the data sources (EA-osts in File 1 and Selling data in File 2). wenn I try this calculation ( sum([Cost EA] * [Units Sold]) ) I'm getting error because of a non-aggegation. I tried the ATTR function but it didn't work (alwasy giving me an error on the multiplication sign)..  Trying ATTR([Costs EA] * [Units]) did not work.

KR,

Heinz

• ###### 3. Re: Wrong SUM Calculation in Profit

If you change the total type to use sum does that work?

Put your formula back to the way you had it and the go to Analysis > Totals > Total All Using > Sum.

Thanks

Ben

1 of 1 people found this helpful
• ###### 4. Re: Wrong SUM Calculation in Profit

Hi Ben,

mhmm - no. I'm more confused as before. I made this new example, can you put your way in this one ?

Regards,

Heinz

• ###### 5. Re: Wrong SUM Calculation in Profit

Ah things begin to get a lot more tricky when blending...

Basically what is happening is that when Tableau calculates the grand totals, it effectively just removes the appropriate dimensions from the view and recalculates, instead of actually summing up the results in the view.

When not blending on a secondary datasource i would usually suggest using an lod to fix your cost total measure for the grand total to work the way you want it to...

But as there is a secondary datasource, the LOD requires all fields to be from the same data source.... So that's not an option....

The next thing I would suggest is generating your own totals in your data set. So effectively add a row per customer that has a product category "Total". Then this will just come through in your visualisation without using the Tableau totals.

I would also look at the 3 part blog by drawing with numbers.

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

They go through the Tableau totals in detail - granted this is from a few years ago, but is still relevant when LODs can't be used.

Let me know if this helps...

Thanks

Ben

• ###### 6. Re: Wrong SUM Calculation in Profit

Hi Ben,

no - I can't fix this.

KR,

Heinz

• ###### 7. Re: Wrong SUM Calculation in Profit

Does somebody have an Idea oder could take a look at the Workbook ?

Regards,

Heinz

• ###### 8. Re: Wrong SUM Calculation in Profit

In this case, you can use Data join instead of Data blend.

Using Prof Calc Test as left join with Product as a key field.

[Costs Total (copy)]

[Costs EA]*[Units Sold]

Thanks,

Shin

1 of 1 people found this helpful
• ###### 9. Re: Wrong SUM Calculation in Profit

Shin, great work that works well - I still have to check if this way also works with Tableau Server connections.

Great thank's to you and also Ben

Heinz

• ###### 10. Re: Wrong SUM Calculation in Profit

So, previous weith two excel sheets works well !

-> But using Live-Data from a Tableau Server and a local Excel-Sheet gives me the same wrong Total Calculation :-( I can't join this to table datas.

Does anybody have a solution for this (a formula or similar) ?

KR,

Heinz