web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / need to create a galle...
Power Apps
Suggested Answer

need to create a gallery in my Power App that displays aggregated summary data

(2) ShareShare
ReportReport
Posted on by MC-04021102-0 4
I am building a Power Apps application that connects to my SharePoint. My SharePoint list contains various columns including REGION (choice column), QUOTE VALUE (USD) (calculated column), FACTORY (choice column), PRODUCT (choice column), and PERIOD QUOTE SUBMITTED (calculated column), among many others. The core challenge I'm facing is that I need to create a gallery in my Power App that displays aggregated summary data - specifically, I want the gallery to show only one row per unique region (instead of all  individual quote records), where each row displays the region name along with the summed total of all quote values for that region. Additionally, I need this summary view to be dynamically filterable using three dropdown controls: one for "PERIOD QUOTE SUBMITTED" (the month/period when quotes were submitted), one for "PRODUCT" (the product type), and one for "FACTORY" (the manufacturing facility). The filtering should work flexibly - meaning I want users to be able to select any combination of filters (one, two, three, or none at all) and the gallery should automatically recalculate and display the regional totals based on only the quotes that match the selected filter criteria. For example, if a user selects "October 2024" as the period and "Product A" as the product, the gallery should show one row for each region, but the totals should only include quotes from October 2024 for Product A. The technical complexity I'm encountering arises from the fact that my SharePoint list columns use the Choice data type, which stores values as record objects with a .Value property rather than simple text strings, and my calculated columns have spaces and special characters in their names (like 'QUOTE VALUE (USD)'), which requires special syntax handling in Power Apps formulas. I was inspired by a similar application I saw that displayed unique areas in a gallery, but that application had a fundamentally different data structure - it used a pre-aggregated SharePoint list where there was already one record per area per period, whereas my data is granular transaction-level data that requires dynamic grouping and aggregation using Power Apps' GroupBy and Sum functions to achieve the same visual result.

any idea on how I could achieve this as I was getting the data for all records when in reality I want them to be grouped
Categories:
Building Power Apps
Error handling
General topics
I have the same question (0)
  • Suggested answer
    Haque Profile Picture
    Haque 3,653 on at
    Hi @MC-04021102-0,
     
    Based on your requirement and explanation, I see there are three areas to address:
    1. Need to handle choice columns are records with a .Value property (e.g., REGION.Value).
    2. Need to handle calculated columns with spaces or special characters require single quotes and brackets (e.g., 'QUOTE VALUE (USD)').
    3. Handle aggregated summary data in the gallery based on some calculated columns.
     
    Let's do an initial draft formula for the followings:
     
    • Use the GroupBy function on your SharePoint list to group records by the REGION column.
    • Use AddColumns to add a new column that calculates the sum of the QUOTE VALUE (USD) for each region group.
    • Bind the gallery to this grouped and summarized collection.
    • Inside the gallery, display the region name and the total quote value.
     
    ClearCollect(
    colRegionSummary,
    AddColumns(
        GroupBy(MySPList, REGION, RegionQuotes),
        TotalQuoteValue,
        Sum(RegionQuotes, 'QUOTE VALUE (USD)')
    )
)

    If we set the gallery’s Items property to colRegionSummary, the grouped results will be displayed. We can use labels inside the gallery to show ThisItem.REGION and ThisItem.TotalQuoteValue.

    Note: Please make sure the QUOTE VALUE (USD) column is numeric or convert it with Value() if needed.

     
    Let's use this formula to create a collection that groups by REGION, filters dynamically by dropdown selections, and sums the quote values:
     
    ClearCollect(
    colFilteredSummary,
    AddColumns(
        GroupBy(
            Filter(
                MySPList,
                (IsBlank(ddPeriod.Selected.Value) || 'PERIOD QUOTE SUBMITTED' = ddPeriod.Selected.Value) &&
                (IsBlank(ddProduct.Selected.Value) || PRODUCT.Value = ddProduct.Selected.Value) &&
                (IsBlank(ddFactory.Selected.Value) || FACTORY.Value = ddFactory.Selected.Value)
            ),
            REGION,
            RegionQuotes
        ),
        TotalQuoteValue,
        Sum(RegionQuotes, 'QUOTE VALUE (USD)')
    )
)
    Bind your gallery’s Items property to colFilteredSummary.
     
    Pleae let me know if  Value('QUOTE VALUE (USD)')) works smoothly.
     
    I am sure some clues I tried to give. If these clues help to resolve the issue brought you by here, please don't forget to check the box Does this answer your question? At the same time, I am pretty sure you have liked the response!
  • Suggested answer
    DP_Prabh Profile Picture
    DP_Prabh 381 on at

    Use Filter → GroupBy → Sum in the Gallery Items property.

    Gallery Item Property

    AddColumns(
    GroupBy(
        Filter(
            'YourSharePointList',
            IsBlank(ddPeriod.Selected.Value) || 'PERIOD QUOTE SUBMITTED' = ddPeriod.Selected.Value,
            IsBlank(ddProduct.Selected.Value) || PRODUCT.Value = ddProduct.Selected.Value,
            IsBlank(ddFactory.Selected.Value) || FACTORY.Value = ddFactory.Selected.Value
        ),
        "REGION.Value",
        "grp"
    ),
    "TotalQuote",
    Sum(grp, 'QUOTE VALUE (USD)')
)
To display values in gallery you can use:
Region : ThisItem.'Region'  // this will return the region value
Total Quote :  ThisItem.TotalQuote  /// this will return the total quote

    Reference:
    GroupBy & Sum(https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-groupby)

    I hope this works for you!

  • Suggested answer
    CarlosFigueira Profile Picture
    CarlosFigueira Microsoft Employee on at
    @Haque / @DP_Prabh neither the AddColumns nor the GroupBy functions use quotes to reference column names - this has changed over a year ago. If you are using AI to assist on the answers, please check that they are correct otherwise users will continue getting confused by it.
     
    @MC-04021102-0 as suggested in previous answers, you will need to use a combination of Filter, GroupBy and AddColumns to accomplish what you need. You can first filter the list based on the solutions (in the expression below it assumes those are done via dropdown controls - see comment (1)). With that, you'll need to first extract the value from the REGION choice column (comment (2) below). Once we have that value, we can group the filtered data by it (3), and finally add a new calculated column that contains the sum of the quote values.
     
    With(
    {
        _data: Filter(        // (1)
            SharePointList,
            And(
                IsBlank(drpPeriodQuoteSubmitted.Selected) Or 'PERIOD QUOTE SUBMITTED' = drpPeriodQuoteSubmitted.Selected.Value,
                IsBlank(drpProductType.Selected) Or PRODUCT.Value = drpProductType.Selected.Value,
                IsBlank(drpFactory.Selected) Or FACTORY.Value = drpFactory.Selected.Value
            )
        )
    },
    AddColumns(                                            // (4)
        GroupBy(                                           // (3)
            AddColumns(_data, RegionValue, REGION.Value),  // (2)
            RegionValue,
            ByRegion
        ),
        TotalQuoteValue,
        Sum(ByRegion, 'QUOTE VALUE (USD)')
    )
)
     
  • DP_Prabh Profile Picture
    DP_Prabh 381 on at
    Thanks for pointing that out — you’re right. I wasn’t aware of that change, and I appreciate the clarification. I’ll make sure to double-check this going forward to avoid any confusion.
    Thanks!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Launch!

Jump in, show your community spirit, and win prizes!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
Valantis Profile Picture

Valantis 424

#2
WarrenBelz Profile Picture

WarrenBelz 355 Most Valuable Professional

#3
11manish Profile Picture

11manish 290

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans