Calculating percent of total with values that count in multiple categories
I'm trying to develop a card for diverse business participation reporting. A single business may have multiple diversity classifications, and can be counted towards each. However, when we are looking at the total, we are looking at the total of distinct businesses.
I want the card to have flexibility to modify the data range, and still have the calculations correct, so I'm trying to do this in beast mode instead of in an ETL. I'm trying to get to something like this (where each example below is the same card but filtered by date range):
I tried taking my dataset and appending rows that were labeled as TOTAL but that didn't work (sum(spend)/sum(case when category = 'TOTAL' then spend end) since at the row level the category is either TOTAL or one of the above. I thought about doing the aggregation in the ETL and creating a new column for total... but given the desire for data flexibility I'm trying to avoid that path.
Has anyone done something like this before or have suggestions?