We are seeking your assistance regarding the computation of the “total” field within our QuickSight visualization. In the attached screenshot, you’ll notice a value referred to as “dsps_with_training_risk_to_deployed” This value can be either 0 or 1.
When displaying totals for our visual, we aim to derive the total for the aforementioned “dsps_with_training_risk_to_deployed” field in a specific manner. Initially, we want this value to be aggregated (sum) at the station level. If the aggregated value at the station level is greater than 0, the corresponding station should be assigned a value of 1; otherwise, it should be assigned 0. Subsequently, the total displayed should be the sum of all station-level values.
At present, the total computation is incorrectly summing all values without adhering to this desired calculation method.
Could you kindly provide us with guidance on achieving this? Your assistance would be greatly appreciated.
First sheet in this excel is basically the data shown in dashboard at the moment.
In the second sheet, I have summed the dsps_with_training_risk_to_deployed field on a station level
3rd sheet is basically the duplicate of 2nd sheet
4th sheet is the final output, where I have applied the logic.
Logic: if the station level aggregate value > 0, then the final output value = 1 otherwise 0.
I want the output in 4th sheet as my Total output in my dashboard
I tried the approach which you shared and I am facing one issue with it. Since I also want to show the total value on Super Region level, apart from Station level, so I used SUM aggregation.
But when I use the SUM aggregation, then it seems like it is adding all 1s instead of showing just 0 and 1.
The super region is an aggregate of the levels below it - not sure what you mean by wanting total value for super region yet a different calculation in the detail.
