cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Migrated_User
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
Migrated_User
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

View solution in original post

Migrated_User
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

View solution in original post

4 REPLIES 4
Migrated_User
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

View solution in original post

Migrated_User
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

View solution in original post

Brian1
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
oyvindmk
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

Helpful resources

Announcements
thirdimage

Power Apps Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

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)