cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EliasMerabet
Advocate II
Advocate II

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

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
RandyHayes
Super User
Super User

@EliasMerabet 

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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

RandyHayes
Super User
Super User

@EliasMerabet 

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)
)

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

8 REPLIES 8
RandyHayes
Super User
Super User

@EliasMerabet 

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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

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

 

RandyHayes
Super User
Super User

@EliasMerabet 

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)
)

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

@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.

EliasMerabet
Advocate II
Advocate II

@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 :):):):):)

RandyHayes
Super User
Super User

@EliasMerabet 

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! 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

#veryfanofyou @RandyHayes 

RandyHayes
Super User
Super User

@EliasMerabet 

Thanks 🙂

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

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