Answered

How to do a group of a Column1, a sum of column2 in a group and use it in a lineal chart

(0) Share

Report

Posted on by Community Power Pla...

Hello Team,

I want to do a group of a column date (column1) and from this group sum all hours (column2) per day. After that I want to show in a lineal chart ==> labels: column1, SeriesA: sum-column2(per a specific date)

Table Column1 Column2
record1 21-10-2021 5
record2 21-10-2021 7
record3 22-10-2021 4
record4 23-10-2021 8

Tabla result:

Table Column1 Column2
record1 21-10-2021 12
record2 22-10-2021 4
record3 23-10-2021 8

With this table I show a linear chart with labels --> 21-10-2021, 22-10-2021, 23-10-2021 and their appropiates values --> 12, 4, 8

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (2)

Verified answer

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (3)

Report
@Anonymous

Consider the following formula for your needs:

AddColumns( GroupBy(yourTable, "Column1", "_data" ), "_hourSum", Sum(_data, Column2) )

You can then use this in the Items property of your chart.

I hope this is helpful for you.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Amazing! @RandyHayes Thanks for helping me

Just one thing. I am returning Column1 as Date with Hours, therefore I want to apply Text(column1, ShortDate) just for showing the date in the chart. How can I achieve in your formula?

Thanks again

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (2)

Report
@Anonymous

The thing about charts and graphs in PowerApps is that you need to shape your data for whatever you need to display.

So, if you are wanting data formatted a certain way for the chart, then do so in the table.

AddColumns( GroupBy(yourTable, "Column1", "_data" ), "_hourSum", Sum(_data, Column2), "_column1Date", Text(Column1, ShortDate) )

I think you might have altered your response about the filtering of the data - I assume you figured that out!

So to sum that up, your Items property formula would be:

AddColumns( GroupBy( Filter(yourTable, column3 && !column4 && column1 > DatePicker1.SelectedDate && column1 < DateAdd(DatePicker1_1.SelectedDate, 1, Days) ), "column1", "_data" ), "_hourSum", Sum(_data, 'column2'), "_column1Data", Text(column1, ShortDate) )

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
@RandyHayes
Yeah, I figure out how to achieve it. Thanks 🙂
In your first example with your code
AddColumns( GroupBy(yourTable, "Column1", "_data" ), "_hourSum", Sum(_data, Column2), "_column1Date", Text(Column1, ShortDate) )
_column1Date looks like doesn´t appear. At least, I donnot have the option in the chart.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@RandyHayes

It works now. I get out after I enter in the application again and it works... crazy...

Thanks again, you are the best @RandyHayes :):):):):)

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report

@Anonymous

Yes, this happens when you change the schema of the items property (on many controls in PowerApps).

Here is the trick that I do when something is not showing that I know should be:

- Select the entire formula and then cut it.

- put in the sample source (for Chart columns - ColumnChartSample, for pie chart - PieChartSample, for a combobox - ComboBoxSample, etc.. )

- Then wipe that out and paste your original formula back

Then magically everything will be there as you expect. Just a little PowerApps quirk!

Was this reply helpful? Yes No
Community Power Pla... on at

Like (1)

Report

#veryfanofyou @RandyHayes

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@Anonymous

Thanks 🙂

Was this reply helpful? Yes No