# How do I get my subtotal and total rows in Table card to show the avg of % rather than the sum of %?

Options
Member
edited April 2023 in Charting

For the column of my table that's using percentages, I'm getting these percentages through a beast mode:

count(`Opportunity.CreatedDate`) /

(case when `Team Months Active` <= 1 then 40

when `Team Months Active` = 2 then 80

when `Team Months Active` = 3 then 120

else 160

end)

For some reason, the Total and Subtotal rows of my table are summing up these averages instead of giving me the average of the total averages. I've tried using Table and Pivot Table cards and it's doing it on both. How do I get the total to reflect the average?

Tagged:

• Coach
Options

@lb1234 Try throwing a SUM around your denominator and see if that fixes it.

**Did this solve your problem? Accept it as a solution!**

• Member
Options

@RobSomers this didn't work.

• Coach
Options

@lb1234 Can you please explain the business logic behind the case statement in your denominator? I suspect you'll have to restructure the data in an ETL to make the subtotal work since your current calculation is only aggregated on the numerator.

• Member
Options

@MichelleH this calculation is to calculate the team's current percentage of their monthly goal. The team monthly goals are ramped up each month from their first month as a team to their 4th month. So, those teams active 1 month or less will have a goal of 40, those with 2 months or less will have a goal of 80 etc.

• Coach
Options

@lb1234 In that case, I recommend using an ETL to aggregate the data by team first so that you have a columns in your dataset for team, count of create date (numerator), and team goal (denominator). Then you can set up a beast mode in your card to calculate the percent of goal, like below:

```sum(`Count of Create Date`) / sum(`Team Goal`)
```