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

lb1234

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?

RobSomers

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

lb1234

@RobSomers this didn't work.

MichelleH

@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.

lb1234

@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.

MichelleH

@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`)