# Aggregate count function does not work in the denominator

Member

When I use the following statement in a card summary, it works properly. But when use it as a value in a Single Value card, the count function doesn't work anymore:

SUM(CASE

WHEN ROUND(100*`Score`/`PossibleScore`)>=60 THEN 1

ELSE 0

END)

/

COUNT( `AttemptNumber`)

For more context, the Attemptnumber column has 450 rows where each row contains a number same number (e.g. 1 or 2, etc). Therefore, I'm expecting to count 450 of them and get 450 in the denominator. But it ends up with the value of only one of the samples (e.g. 1 or 2, etc). I hope I explained it clearly.

Oddly enough, when I changed the division operation to addition it works! Any suggestion and comments are highly appreciated.

Thanks,

Musetti

Tagged:

• Coach

@Musetti Do you have any sorting or grouping in your Single Value card? If you do, try removing it and see if that fixes the issue.

• Contributor

@Musetti, Since you're looking to make a percentage, try removing the 100* from your beast mode and then change the format of your value to be a percentage.

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

• Member

@MichelleH you just solved the problem! I removed all of the sorting and now it gives the right number. Amazing!

@RobSomers that is actually great idea. Thanks!

Musetti

• edited November 2022

Hi all,

I have issue with using aggregation formula in Single Card Value.

• When I create a Beast Mode metric with rule "SUM(`OVERALL_SCORE`+0) over (partition by `VISIT_ID`)" => Drag into Single Value card => It displays 770
• When I create a Beast Mode metric with rule "COUNT(DISTINCT `VISIT_ID`)" => Drag into Single Value card => It displays 8

However, when I create a Beast Mode metric with following rule and drag to Single Value Card

SUM(`OVERALL_SCORE`+0) over (partition by `VISIT_ID`)

/

COUNT(DISTINCT `VISIT_ID`)

=> result is 323.33 which is incorrect. It should be 96.25 (770/8).

Could anyone help me with this issue? Thanks a lot!!!