I’m trying to perform a table calculation which will sum the number of outcomes missed from the granular level of detail of incident number to the overall level of Area and service.
The data works as 1 incident can have many outcomes missed, this outcome will have 1 area and service associated with it; however the same outcome can be missed across more than 1 incident and the number missed can be from 1 to infinity.
At present my data is set up with other information so for one outcome there can many rows with different additional data, albeit the outcomes information is a repeat.
I am trying to show the number of outcomes missed at each level but the way my calculation works at the minute cannot cope with instances where more than 1 outcome was missed. In excel I would utilise the sumproduct function but I have not found a way to replicate this for the table calc.
Attached is the Tableau workbook with the calculation as I currently have it (which only works at the outcome level) and also attached is an excel workbook showing the way I would calculate the measure needed using excel (calcs at row level on sheet 'Outcomes_Data' then aggregated using the pivots tables on sheet 'Outcomes Data Required'). The main issue I think I have is trying to mix aggregate and non-aggregate measures.
The calculation results for number of outcomes must always be a whole number - that's how I have spotted there is an error in the calculation.
Any help on this would be really appreciated as I have been struggling for some time with this!