cancel
Showing results for
Did you mean:
Level 10

## Groupby - Summation

I find this Groupby feature very useful for data manipulation.

When I use Gallery to show "CitiesByCountry" (from Groupby collection) and wish to list out: By Country and Total Population (of all the cities of this country), I am stuck!

- Country can be shown easily with ThisItem.Country; What about the formula for the Summation of Population (of all the cities of the particular country?)

PK Hong

2 ACCEPTED SOLUTIONS

Accepted Solutions
Level 10

## Re: Groupby - Summation

To get the sum of the population you'll need to use the following expression

`Sum(<expression that gets the grouped table for the specific country>, Population)`

For example, if you have a gallery like the one below, with the Items property set to CitiesByCountry:

The first text box (larger font) has its Text property set to ThisItem.Country. The second textbox (displaying the total population has the Text property set to Sum(ThisItem.Cities, Population).

Carlos Figueira

Level 10

## Re: Groupby - Summation

Thanks...MAGIC! MAGIC!

I wrote the same formula Sum(ThisItem.Cities, Population) but it appeared to be error. WHY?

But when I re-do as you said, OK!

I guess, sometimes, as of my experience, I need to either RETYPE, or Quit the program and re-Load.

Strange, Strange, Strange!

BUT, anyway, thanks a lot.

NB: In fact, I am grouping more than that, e.g.:ClearCollect(FarmHouse, Groupby(TableFarm, "FarmName", "HouseNo", "HouseQty), so that I can have double FILTERING of FarmName - then - HouseNo, to get the Sum of HouseQty.

This is a very useful features for multi-layer data manipulation.

Thanks.

PK Hong

4 REPLIES 4
Level 10

## Re: Groupby - Summation

To get the sum of the population you'll need to use the following expression

`Sum(<expression that gets the grouped table for the specific country>, Population)`

For example, if you have a gallery like the one below, with the Items property set to CitiesByCountry:

The first text box (larger font) has its Text property set to ThisItem.Country. The second textbox (displaying the total population has the Text property set to Sum(ThisItem.Cities, Population).

Carlos Figueira

Level 10

## Re: Groupby - Summation

Thanks...MAGIC! MAGIC!

I wrote the same formula Sum(ThisItem.Cities, Population) but it appeared to be error. WHY?

But when I re-do as you said, OK!

I guess, sometimes, as of my experience, I need to either RETYPE, or Quit the program and re-Load.

Strange, Strange, Strange!

BUT, anyway, thanks a lot.

NB: In fact, I am grouping more than that, e.g.:ClearCollect(FarmHouse, Groupby(TableFarm, "FarmName", "HouseNo", "HouseQty), so that I can have double FILTERING of FarmName - then - HouseNo, to get the Sum of HouseQty.

This is a very useful features for multi-layer data manipulation.

Thanks.

PK Hong

Level: Power Up

## Re: Groupby - Summation

Hi.. if you have a data source going to a collection - the collection is called SumSalesbyProduct, with three columns Product, Date, and Sales. How do I sum the sales per Product to get the overall sales per Product. Thanks.
Highlighted
Level: Powered On

## Re: Groupby - Summation

I do not know how to use the Collection for get a partial sum,

but if you want to show the sum of a GroupBy function in a Gallery, you can use the AddColumns function to create a new field with the sum that can be used to show the sum of underlying data in the Gallery.

The Gallery definition could for example be:

Items = AddColumns(GroupBy(DATASOURCE, "FIELDTOGROUPBY","NAMEOFGROUP"),"SUMCOLUMN", Sum(NAMEOFGROUP, FIELDTOSUM))

Then you can add a label in the gallery with:

Text = Thisitem.SUMCOLUMN

Announcements

#### Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

#### Power Platform World Tour

Find out where you can attend!

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,848)