Could you please attach sample data as twbx format?
See the attached
this formula is a table calculation
is this what you expected?
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
Hi Jim, I see the % is 58.55, when it should be 29.62 for the first total line.
I am taking $175.50/592.50 and getting 29.62. Thats what all the totals should calculate by, the total $ diff div by the total paid dollars...
First of all, Tableau is not good at all to create Table format view like Excel.
Based on my experience, this is "do-able" with 1000 hours efforts maybe, means not realistic to chase by Tableau.
When you have Conditional sum, sub total or G.Total logic becomes different because of the layer of aggregation.
To make sub/G.total correct, I usually use technique of duplicating data with Union PROVIDED number of measure value is a few.
Once duplicate the data, all other measure values needs to be re-build.
Re-build does not mean "modify". need to add another set of all the measure value.
Seeing your data, it's not realistic...
I will not analyze all of your calculation formulas but you may try using "Tableau prep"..
The problem is most likely due to [Total Billed $] being pre-aggregated. Table calcs could work but it may be fiddly. LOD is the next best approach.
There are many filters in the worksheet. So, I have added all the dimensions to the LODs. This will allow the calculations to respond to each filter. Your data is very granular. So, calculating the LOD expression at similar level of granularity is essential. See attached workbook.
Hope this helps.
Thank you Ossai, that did the trick, and I have marked it as correct answer. One more question since I have your attention. There is a measure named sage invoice which I previously had after %diff but now I moved it to a dimension after WO#. The reason being is it was repeating the sage invoice amount on every line, but I just want to display it as a subtotal for every WO# only.
Is there a way to hide the detail for every line but just display the subtotal?
I attached a your workbook for you to see what I mean.