What is the formula in the 1st and 2nd calculated fields? Also, why do you say 4th column is wrong? What are your expected results for the 4th column? If you are creating a duplicate of the 3rd column and doing a percent of total, for the 1st value I would expect to see 200/1550 = 12.90 which is what you are getting. So I am not sure, what else would you expect to see? Unless, you are missing adding a specific detail about your dataset here in your example.
You maybe misunderstood dimension vs. measure, discrete vs. continuous and aggreation value.
I made a sample data set and crosstab sheet for you. Please see a attached file.
I hope it would be a great help to your problem!
Test Set.twbx 11.4 KB
Thanks for your response. Below is the output of tableau report I created.
I followed below steps for Column1 in above report.
Step1: I created below calculated field named "Last 90 Days Notional" which would accumulate only those "Total Value USD" which correspond to last 90 days.
Below is the code for calculated field:-
if DATEDIFF('day',[Date],TODAY()-DATEPART('weekday',TODAY())-1)<90 then [Total Value Usd] else 0 END
Step 2: Then, I created a "calculation" in Measure Values card, formula for which is:
Sum(Last 90 Days Notional)
Similarly, I repeated Step 1 and Step 2 for Column2 (trading days in last 90 cal days) in the tableau report.
I created calculated field named "Last 90 Days Trading Days" and added a similar code.
I then created a "new calculation" in Measure Values formula for which is:
Sum(Last 90 Days Trading Days)
I tried creating a calculated field for Column3 also, but it didn't return expected results. So I simply added a "new calculation" to Measure Values card formula for which is:
Sum(Last 90 Days Notional) / Sum(Last 90 Days Trading Days)
Everything is as per the expectations till column3 (you can cross check the calculations in tableau report as well). Only problem I am facing is for Column4.
I tried copying calculation for Column3 in Measure Values card and then added Table Calculation to it in order to see % Value of Total.
Column4 Formula Generated by Table Calculation: (highlighted in the second screenshot)
SUM([Last 90 Days Notional])/SUM([Last 90 Days Trading Days]) / TOTAL(SUM([Last 90 Days Notional])/SUM([Last 90 Days Trading Days]))
Formula seems correct. But results are incorrect.
Thanks in advance.
Thanks I read your solution. As per your suggestion, I need to convert all measures to dimensions.
But the problem is I am calculating all 4 Columns. Hence, I would have to add table calculation (% of total) on a Measure.
It has worked for me in the past. But I guess, I am facing issues because of "AGG" measure. Column3 is AGG a measure computed from Column 1&2.
Could you please check my reply to Pooja. I have tried to explain in the depth the issue I am facing with Column4.
Thanks for such a detailed explanation but I have to admit, I don't fully understand whats going on with the formulas because while using table calcs a whole lot depends on how pills are placed on the view and how partitioning/addressing is set. And this is too much information for me to create a dummy workbook. I recommend attaching your workbook so someone can help.
Also, which version of Tableau are you using?
Hey Thanks Pooja for being so much prompt in replying. And yes I can understand info I shared was too much, but I didn't want to miss anything.
I am an Intern so need to which if I can share actual data with you. I can create dummy transactional data, but it would take some time.
Tableau version I am using is 9.2
Appreciate your effort of creating a dummy dataset, I am back to my initial question though, the results do look right to me like I said in my 1st post. Wouldn't 136,087,683/4,805,287,410 = 2.83%? That is what you getting for platform B. I don't know what different are you expecting?
Thanks for pointing that out.
It seems the issue is different now. Not with % of total calculation.
Third column "total" as highlighted below is incorrectly computed.
Surprisingly , Grand Total is less than line item value.
Probably, I need to change the title of the thread as well now that the issue is totally different.
Ok I think I know the problem. The results are absolutely right as to what you see on the view. The cell you boxed in red is the result of 4,651,518,212,268/968 = 4,805,287,409 from the 1st 2 columns. Its not the addition of values in the 3rd column itself which would be 19,274,713,182 if you click on the cell and compute using sum instead of automatic:
So what you technically want is: (sum([2015 Notional])/sum([2015 Trading Days]))/window_sum([Calculation1])
Division by window_sum of the calculation 1 which is (sum([2015 Notional])/sum([2015 Trading Days]))
and you end up getting correct values.
So basically for platform B it would be 136,087,683/19,274,713,182 = 0.71%
See attached version 9.2 WB.
dummy.twbx 809.7 KB
I found the solution.
I tried changing sum from automatic to window sum and it worked.
Thanks for all the help. Have a great weekend
Yes, I tried a very similar thing few min back.
This is what I used for Column4:
SUM([2015 Notional])/SUM([2015 Trading Days]) / WINDOW_SUM(SUM([2015 Notional])/SUM([2015 Trading Days]))
Effectively it is the same thing as what you suggested.