I am working from Tableau public and am very new to Tableau so I apologize in advance. I am not sure how to post the workbook so if you can help me on how to do this I can otherwise I will post screenshots.
I created a calculated field called [ Monthly Total Fees] which takes the annual total fee and breaks this down to a monthly view. The months for the columns indicates when the sales deal was completed.
From the [Monthly Total Fees] I then create another calculated field [calculation 1] which takes the monthly total fees that should be collected for that month. Here is the formula I used:
IF FIRST() = 0
THEN [Monthly Total Fee W/ 0's]
ELSEIF LOOKUP([Monthly Total Fee W/ 0's],0) = 0
ELSE [Monthly Total Fee W/ 0's] + PREVIOUS_VALUE(INDEX()-1)
The issue I am getting is that each month calculates correctly for how much each sales rep will collect monthly but the grand total displays only the last monthly total fee:
I then created another calculated field that uses Windows_Sum and this displays the correct grand total is displayed on each month instead of the grand total. Here is the formula and screenshot:
I have read a lot of articles including these listed below and they helped in getting the calculation but with no success for the grand total unfortunately
Can you please upload the twbx file..