cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
moelhag
Microsoft
Microsoft

Display average of column dependent on time period selected.

I have page where it shows capacity for specific people dependent on hours entered for a period. I want to display a text label on the right side of my page that displays the average capacity for the group dependent on what time period is chosen. Both of these columns are present on the SharePoint list below:

 

capacitypercentage.PNG

 

My code looks something like this, but isnt running:

 

Last(Filter('Capacityform',PeriodName = Dropdown3.SelectedText.Value,Average('CapacityForm',CapacityPercentage)* 100)

 

Basically, I want it to find the average of the percentages on the right column (circled in blue) dependent on the time period selected(circled in red on right).

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-siky-msft
Community Support
Community Support

Hi @moelhag ,

Based on your description, please try this code:

Average(Filter('Capacityform',PeriodName = Dropdown3.SelectedText.Value),CapacityPercentage)

If you want the Average value formated as percentage, please try this:

Text(Average(Filter('Capacityform',PeriodName = Dropdown3.SelectedText.Value),CapacityPercentage),"[$-en-US]##.00%")

Hope this helps.

Sik

View solution in original post

5 REPLIES 5
WarrenBelz
Super User III
Super User III

Hi,

If these can be display the filtered period in a gallery, try

Average(YourGalleryName.AllItems,CapacityPercentage)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

v-siky-msft
Community Support
Community Support

Hi @moelhag ,

Based on your description, please try this code:

Average(Filter('Capacityform',PeriodName = Dropdown3.SelectedText.Value),CapacityPercentage)

If you want the Average value formated as percentage, please try this:

Text(Average(Filter('Capacityform',PeriodName = Dropdown3.SelectedText.Value),CapacityPercentage),"[$-en-US]##.00%")

Hope this helps.

Sik

View solution in original post

Hi @moelhag ,

My first response was focused on your gallery rather than the code below.

One thing I would look at is the drop-down syntax as well - so extrapolating my earlier code to the filter required.

Average(
   Filter(
      'Capacityform',
      PeriodName = Dropdown3.Selected.Value
   ),
   CapacityPercentage
)

I agree with @v-siky-msft 's suggestion on the formatting display.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Hey @v-siky-msft 

 

This code worked great. Thanks for the help.

 

One last thing, the formatting code formatting the text to ".#%'. How would i go about removing the period before the percentage?

 

See example below:

 

percentage mix up.PNG

 

Best,

 

Moaz

Hi @moelhag ,

 

Please try this:

If("." in Average(Filter('Capacityform',PeriodName = Dropdown3.SelectedText.Value),CapacityPercentage)*10, 
Text(Average(Filter('Capacityform',PeriodName = Dropdown3.SelectedText.Value),CapacityPercentage)*100,"[$-en-US]##.#%"),
Text(Average(Filter('Capacityform',PeriodName = Dropdown3.SelectedText.Value),CapacityPercentage)*100,"[$-en-US]##%"))

Hope this helps.
Sik

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Carousel April Dunnam Updated 768x460.jpg

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors
Users online (2,968)