cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SteveDK
Frequent Visitor

Calculate average on gallery records

Hi,

I have 2 questions about a gallery to show in my app. My app is to track time on a physical check of items.
I'm tracking the CheckTime for the 10 first times an item is checked.

Is there a way I can show a gallery with the checked items (only showing the Title once) and also show the average of that item 10 times check records? If possible to calculate the average even if that item isn't checked for 10 times yet?

My sharepoint list is build as example below.

SteveDK_1-1612430128875.png

 

Any suggestions are welcome.

Thank you,
Steve

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-yangar-msft
Microsoft
Microsoft

Hi @SteveDK ,

 

Based on your screenshot of your SharePoint List, I created a same list as yours:

v-yangar-msft_0-1612517008597.png

 

Here I use CheckIngTime instead of CheckTime because they are both calculated column and dont have the value of second.

For your first issue, you can set the items  property of gallery to:

Distinct(TimeTrack,Title)

And it will distinct the duplicated record and only show the Title once.

 

For your second issue and third issue, firstly, you should get two values: the amount of checked items and the total of checking time.

Add a label and set the Text property to:

  Sum(
        TimeTrack,
            Value(
                First(
                    Split(
                        CheckIngTime,
                        ":"
                    )
                ).Result
            ) * 60 + Value(
                Last(
                    Split(
                        CheckIngTime,
                        ":"
                    )
                ).Result
            )
        )/ CountIf(TimeTrack,":"in CheckIngTime)

//This formula aims to get the average of checking time

 

Finally, we need to format the value of average to mm:ss and set the Text property to:

Concatenate(First(Split(Text(Sum(
        TimeTrack,
            Value(
                First(
                    Split(
                        CheckIngTime,
                        ":"
                    )
                ).Result
            ) * 60 + Value(
                Last(
                    Split(
                        CheckIngTime,

                        ":"
                    )
                ).Result
            )
        )/ CountIf(TimeTrack,":"in CheckIngTime)
),".").Result).Result,":",Text(Value(Last(Split(Text(Sum(
        TimeTrack,
            Value(
                First(
                    Split(
                        CheckIngTime,
                        ":"
                    )
                ).Result
            ) * 60 + Value(
                Last(
                    Split(
                        CheckIngTime,
                        ":"
                    )
                ).Result
            )
        )/ CountIf(TimeTrack,":"in CheckIngTime)
),".").Result).Result*6)))

 

v-yangar-msft_1-1612517008599.png

 

 

Hope it helps!

 

Thanks,

Arrow

View solution in original post

5 REPLIES 5
v-yangar-msft
Microsoft
Microsoft

Hi @SteveDK ,

 

Based on your screenshot of your SharePoint List, I created a same list as yours:

v-yangar-msft_0-1612517008597.png

 

Here I use CheckIngTime instead of CheckTime because they are both calculated column and dont have the value of second.

For your first issue, you can set the items  property of gallery to:

Distinct(TimeTrack,Title)

And it will distinct the duplicated record and only show the Title once.

 

For your second issue and third issue, firstly, you should get two values: the amount of checked items and the total of checking time.

Add a label and set the Text property to:

  Sum(
        TimeTrack,
            Value(
                First(
                    Split(
                        CheckIngTime,
                        ":"
                    )
                ).Result
            ) * 60 + Value(
                Last(
                    Split(
                        CheckIngTime,
                        ":"
                    )
                ).Result
            )
        )/ CountIf(TimeTrack,":"in CheckIngTime)

//This formula aims to get the average of checking time

 

Finally, we need to format the value of average to mm:ss and set the Text property to:

Concatenate(First(Split(Text(Sum(
        TimeTrack,
            Value(
                First(
                    Split(
                        CheckIngTime,
                        ":"
                    )
                ).Result
            ) * 60 + Value(
                Last(
                    Split(
                        CheckIngTime,

                        ":"
                    )
                ).Result
            )
        )/ CountIf(TimeTrack,":"in CheckIngTime)
),".").Result).Result,":",Text(Value(Last(Split(Text(Sum(
        TimeTrack,
            Value(
                First(
                    Split(
                        CheckIngTime,
                        ":"
                    )
                ).Result
            ) * 60 + Value(
                Last(
                    Split(
                        CheckIngTime,
                        ":"
                    )
                ).Result
            )
        )/ CountIf(TimeTrack,":"in CheckIngTime)
),".").Result).Result*6)))

 

v-yangar-msft_1-1612517008599.png

 

 

Hope it helps!

 

Thanks,

Arrow

Hi,
@v-yangar-msft 

Thank you for your reply.

 

So what I did is to set my CheckTime column calculated formula in my Sharepoint list to just aim for hh:mm        =TEXT(StopTime-StartTime,"hh:mm")
because it had the seconds value included in the formula and didn't show me any outcome.

Then added your formula to calculate the average to my gallery in a text label and adapted the list name and column name to my settings but received an identic calculation for each item in my gallery and doesn't seem to show me the correct outcome. For example the outcome for the first record on the below print screen should be 4.
I think i'm still overlooking something 🤔

SteveDK_1-1612618656491.png


Kind regards,

Steve

 



Drrickryp
Super User
Super User

HI @SteveDK 

To get the hrs and minutes from the DateDiff() is not as simple as it might seem @CarlosFigueira  came up with this formula.  You use DateDiff() twice, once for hours and once for minutes. 

 

DateDiff(
    dtStart.SelectedDate + Time(Value(ddHourStart.Selected.Value), Value(ddMinuteStart.Selected.Value), 0),
    dtEnd.SelectedDate + Time(Value(ddHourEnd.Selected.Value), Value(ddMinuteEnd.Selected.Value), 0),
    Hours) &
":" &
Mod(
    DateDiff(
        dtStart.SelectedDate + Time(Value(ddHourStart.Selected.Value), Value(ddMinuteStart.Selected.Value), 0),
        dtEnd.SelectedDate + Time(Value(ddHourEnd.Selected.Value), Value(ddMinuteEnd.Selected.Value), 0),
        Minutes),
    60)

  

v-yangar-msft
Microsoft
Microsoft

Hi @SteveDK ,

 

If you have further questions, you can post a new case and we can continue to help you in this hand.

 

Thanks,

Arrow

Hi,
@v-yangar-msft 

With some adaptions to personal needs this finally works for me.
Thank you very much for your help.

Kind regards,

Steve  

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (2,622)