Please refer the attached data source and the worksheet.
In the Excel, for a 'Participant' the status can be either 'Attended' or 'Walk In' and they have a capacity assigned to it. For the data highlighted, we need the final capacity to be '25'. And for cases where the capacity for status in 'Attended' in zero and in 'Walk In' is 5; we have to assign capacity as '5'.
|Attended Capacity||Walk In Capacity||Desired value in Tableau|
By applying some math functions, we were able to create 2 columns for the capacities in status 'Attended' and 'Walk In'; and chose the maximum value of the 2 as the final capacity. But the problem was the grand total (as shown in the attached Tableau worksheet), even that value was the max. The sum of the final capacity should be the grand total instead of the maximum value, can you please find a solution or an alternate to this problem.