I'm glad you figured it out! That's definitely a way to get there, and probably the way I'd approach it because it's explicitly referencing the value of the Attempt dimension, so if the sort of the view changes the table calc results wouldn't change. One optimization would be to put the IIF([Attempt]="First time renewal",[Totals],0) into a separate calculated field, that way if you are using a Tableau data extract that field will be materialized in the extract and be faster.
There are often multiple routes to the same solution, here are three others:
1. Another way to get a specific reference within a partition is to use the LOOKUP() function, like:
LOOKUP(SUM([Totals]),FIRST()) with a Compute Using of Attempt or Table (Down) would return the value of SUM([Totals]) for the first row (address) in the partition.
2. In this case, where you're wanting to return the value of the first row to every other row, another option would be to use:
Again with a Compute Using of Attempt or Table (Down).
3. You could also set up a data blend with a duplicated data source, where the secondary source is filtered to just the First time renewals and Attempt is not one of the linking fields. Then you could use the Totals from the secondary source with an accurate result for every row. I've used this at times when table calculations would be too slow.
Can I use your logic in this case please see my explanation below.
I am trying to solve an error problem which I have done in excel a few times in the past but can't get it to work in tableau. Mock data consists of days out which goes out 118 days out. So when it comes to day 0 that means 'on the day of '.
Units sold- # of units sold e.g. on day 5, 121 units were sold
projected units to be sold- is my forecasted units to be sold. e.g. day 5 i am forecasting 198
then i have deviation and absolute of that
Where I am stuck is the absolute error. What I am looking for is:-
Day 5- 198 forecast units and it should refer to what was actually sold ' on the day of ' which is 168 units
hence 198- 168= 30 units variance
then that 30/168= 17.8% is my forecast error
And this should apply to all days in the ' projected units to be sold ' refer to the final number on day '0' which in this case is 168 in units sold. so all numbers in forecast column to one final number in units sold.
Please any help is appreciated.
I have replied to your original thread.
Thank you for the solution.