Unanswered

Calculate Average excluding records with value of Zero.

Like (0) Share

Report

Posted on 12 Oct 2023 14:41:36 by TONYSTORM

Hello everyone. I have a gallery that shows an average rating for each manager. Basically, it's looking at a Dataverse table and averaging the ratings sent from each assigned employee. I have a code that is working, but I'm having an issue trying to add a code that ignores any ratings with a zero value. This is the code I have so far. Thanks!

RoundUp(Average(Filter(DATA_REPORTS,Manager=ThisItem.Value),Avg_Rating),2)

Categories:

General Questions

All responses (6)

Answers (0)

TONYSTORM 49 on 12 Oct 2023 at 17:34:45

Like (0)

Report
Re: Calculate Average excluding records with value of Zero.

Does this issue only occur when adding the 'Avg_Rating <> 0' code?
Answer: Yes, when I add the code It returns with only '1' as a value
What column type is 'Manager' and what does 'ThisItem.Value' refer to?
Answer: Manager Column is a Text field and ThisItem.Value refers to the Manager that is being used for the Distinct function
LaurensM 12,510 Super User 2025 Season 1 on 12 Oct 2023 at 16:01:36

Like (0)

Report

Re: Calculate Average excluding records with value of Zero.

No worries! For further troubleshooting, would it be possible to explain the set-up a bit more in regards to my 2 questions (see previous comment)?

Thanks! 😊
TONYSTORM 49 on 12 Oct 2023 at 15:47:02

Like (0)

Report

Re: Calculate Average excluding records with value of Zero.

I forgot to mention, this is averaging from a gallery that is filtered with the Distinct function. Sorry about that.
LaurensM 12,510 Super User 2025 Season 1 on 12 Oct 2023 at 15:22:35

Like (0)

Report
Re: Calculate Average excluding records with value of Zero.

@TONYSTORM thank you for the info, this is indeed behaviour I would not expect from the given code.

Does this issue only occur when adding the 'Avg_Rating <> 0' code?

What column type is 'Manager' and what does 'ThisItem.Value' refer to?
TONYSTORM 49 on 12 Oct 2023 at 15:15:27

Like (0)

Report

Re: Calculate Average excluding records with value of Zero.

Thanks for replying so quickly. No errors popped up, however, its showing an average of "1" for all managers.
LaurensM 12,510 Super User 2025 Season 1 on 12 Oct 2023 at 14:49:07

Like (0)

Report
Re: Calculate Average excluding records with value of Zero.

Hi @TONYSTORM,

Could you give the following code a try:

RoundUp( Average( Filter( DATA_REPORTS, Manager = ThisItem.Value, Avg_Rating <> 0 ), Avg_Rating ), 2 )

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!