Solved: Calculating Average on records

gokulnath · ‎05-04-2022

Hi,

I am on Power Apps development. I need to calculate the average rating for users (screenshot 1). In the below screen you can see 'Aiden' got 2 proficient ratings, So the average rating should be 'Proficient'. how to do that. any idea ?

Ex: User got 1 response for strength, 2 response for proficient , 5 response for insufficient response , average rating should be 'insufficient'.

More details: We are showing all users and average rating for each user in gallery.

Expression used:

ClearCollect(colResponses1,Filter(Responses_List,UserPrincipleName = "Aiden@xxxxx.com"));
ClearCollect(
colGroupedResponses1,

AddColumns(
GroupBy(
colResponses1,
"FullName",
"UserPrincipleName",
"GroupedItems"
),
"CountValue",
CountRows(GroupedItems),"AvgRating",LookUp(colRatings, RatingID= Round(Average(GroupedItems,RatingID),0)).RatingName
)

);

colRatings:

WarrenBelz · ‎05-11-2022

@gokulnath ,

Yes, but what about my example and others where an equal amount of votes are given to the two top values ? I am trying to point out that you need a more complex rule than that, or you will simply not get the result you need. The below should work if you only have one value with the most votes

ClearCollect(
   colTest,
   AddColumns(
      GroupBy(
         Filter(
            Responses_List,
            UserPrincipleName = "Aiden@xxxxx.com"
         ),
         "FullName",
         "UserPrincipleName",
         "GroupedItems"
      ),
      "AvgRating",
      With(   
         {
            wRate:
            First(
               Sort(
                  AddColumns(
                     GroupBy(
                        GroupedItems,
                        "RatingID",
                        "Data"
                     ),
                     "RateNo",
                     CountRows(Data)
                  ),
                  RateNo,
                  Descending
               )
            ).RatingID
         },
         LookUp(
            colRatings,
            RstingID = wRate
         ).RatingName
      )
   )
)

View solution in original post

Pstork1 · ‎05-05-2022

I think you need to replace the Round() in your formula with RoundDown(). The average is coming out with a number like 2.5 which Round is Changing to 3.0. RoundDown() would change it to 2.0. For the rating system I think you always want to round down in terms of rating. Round will round up if the fraction is .5 or more and down if its .4 or less.

-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

gokulnath · ‎05-05-2022

Hi @Pstork1 ,

Thank you for your response. I tried RoundDown(), but it still returns the incorrect average.

Pstork1 · ‎05-05-2022

What average does it give you?

-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

gokulnath · ‎05-06-2022

Hi @Pstork1 ,

It gives me 'Needs Improvement' rating which is wrong.

Pstork1 · ‎05-06-2022

Yes, but what is the numerical value that is calculated by the AVG().

-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

gokulnath · ‎05-06-2022

Hi @Pstork1 ,

I couldn't find the value.

Pstork1 · ‎05-06-2022

Temporarily remove the Lookup from your additional column and have it print the actual average. Until you can see what the rating average actually is you can't determine why its picking the wrong value on the lookup.

-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

WarrenBelz · ‎05-06-2022

@gokulnath ,
As per your PM - please follow @Pstork1 's advice - there is nothing wrong with your formula structure - if you run this

ClearCollect(
   colTest,
   AddColumns(
      GroupBy(
         Filter(
            Responses_List,
            UserPrincipleName = "Aiden@xxxxx.com"
         ),
         "FullName",
         "UserPrincipleName",
         "GroupedItems"
      ),
      "RatingAvg",
      RoundDown(
         Average(
            GroupedItems,
            RatingID
         ),
         0
      )
   )
);

you will see the underlying value.