Calculating percent of total with values that count in multiple categories

mhouston

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.

Example data:

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?

MarkSnodgrass

@mhouston assuming you have window functions in beast mode enabled in your instance, you should be able to use this to get the percent of total:

sum(amount)
/
sum(sum(amount)) over ()

mhouston

@MarkSnodgrass how would you structure the over clause? I couldn't figure out how to do that to get the amount for the distinct businesses.

MarkSnodgrass

Try adding a partition by if you want to be the percent of that business name

sum(amount)
/
sum(sum(amount)) over (PARTITION BY `BusinessName`)

mhouston

I don't want it to be a percent of business name, but the total spend for the category divided by the total spend across all distinct businesses. So like in the example data I put, I want the total women owned business spend (1000+3000) divided by the total spend across distinct businesses (1000+2000+3000) = 67%. The 1000 spend for business A can be counted in each category (small, woman, minority), but we've only spent it once, so in the total dollars it should only be counted once.