cancel
Showing results for
Did you mean:
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.

Any suggestions are welcome.

Thank you,
Steve

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

Hi @SteveDK ,

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

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

Hope it helps!

Thanks,

Arrow

5 REPLIES 5
Microsoft

Hi @SteveDK ,

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

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

Hope it helps!

Thanks,

Arrow

Frequent Visitor

Hi,
@v-yangar-msft

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 🤔

Kind regards,

Steve

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

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

Frequent Visitor

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

Announcements

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