In the table below, I want to create a visualisation whereby for each 'Cause' category, it shows the total number of cases (excluding any duplicates). For example, for the 'Wind' category, the count should be 2 and not 4, as case 001 and 002 both fall under this.
I'm assuming I need to create a calculated field but I'm not too sure how to go about this. Just to flag, I will also be adding a 'State' filter on the visualisation so multiple or single states can be selected in the view of the visualisation - I'm not sure if this would impact the type of formula used. Any help or direction would be greatly appreciated.
Hi @Amy Afriyie,
You can do this with a distinct count of the Case ID, that will only return one number for each case. This will still work if you filter to state and will show a count of the individual cases under that state.
COUNTD([Case ID])
