cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JasonF
Level: Powered On

Summing like series in a chart

Hi Everyone,

Just started playing with charts. It appears there is no way sum series of the same label. Am I missing something here

 

In this Example I am looking to have food summed as one label:

Charts.JPG

 

1 ACCEPTED SOLUTION

Accepted Solutions
mdevaney
Level 8

Re: Summing like series in a chart

@JasonF 

Unfortunately, summarization of data is not a feature of PowerApps charts.  Are you familar with Excel?  The charts in PowerApps behave exactly like an Excel chart: the data supplied is the data shown.  Perhaps you also know about PivotCharts in Excel?  There's is an equilvant to this in PowerApps as well: Power BI tiles.  In Power BI you can definitely sum the data using the Power Query language and then use the tile in your app.

 

I think there are 3 ways to go here:

  1. If your datasource is less than 500 records it can be summarized by creating a collection, using GROUPBY to group the data by sales type and SUM to calculate the numbers.  There's an example of this in the documentation.
  2. If your datasource is greater than 500 records make a PowerBI tile.  I do not have any experience in this area yet but I know it can be done.
  3. Make a new table in your datasource with one record per sales type you want to summarize by.  Each time a new sale is added/deleted use the PATCH statement to update the total sales for the record in your new table.

 

That's my general suggestions on approaching the problem.  What option sounds best to you?

 

Note: I don't know much about your app but are you just trying to build a dashboard?  If this is the sole purpose for your app I would suggest using PowerBI instead. 

 

---
Please click "Accept as Solution" if my response helped to solve your issue so that others may find it more quickly. If your thought the post was helpful please give it a "Thumbs Up."

View solution in original post

5 REPLIES 5
mdevaney
Level 8

Re: Summing like series in a chart

@JasonF 

Unfortunately, summarization of data is not a feature of PowerApps charts.  Are you familar with Excel?  The charts in PowerApps behave exactly like an Excel chart: the data supplied is the data shown.  Perhaps you also know about PivotCharts in Excel?  There's is an equilvant to this in PowerApps as well: Power BI tiles.  In Power BI you can definitely sum the data using the Power Query language and then use the tile in your app.

 

I think there are 3 ways to go here:

  1. If your datasource is less than 500 records it can be summarized by creating a collection, using GROUPBY to group the data by sales type and SUM to calculate the numbers.  There's an example of this in the documentation.
  2. If your datasource is greater than 500 records make a PowerBI tile.  I do not have any experience in this area yet but I know it can be done.
  3. Make a new table in your datasource with one record per sales type you want to summarize by.  Each time a new sale is added/deleted use the PATCH statement to update the total sales for the record in your new table.

 

That's my general suggestions on approaching the problem.  What option sounds best to you?

 

Note: I don't know much about your app but are you just trying to build a dashboard?  If this is the sole purpose for your app I would suggest using PowerBI instead. 

 

---
Please click "Accept as Solution" if my response helped to solve your issue so that others may find it more quickly. If your thought the post was helpful please give it a "Thumbs Up."

View solution in original post

JasonF
Level: Powered On

Re: Summing like series in a chart

Dear Mdevaney,

Thank you for your reply. At this stage I am struggling learning PowerApp (beginner) and not too keen on going out and learning PowerBI at the same time. In the old days a simple SQL query would solve my problem, but powerApps works on different principals to make a more intuitive user interface. However, before I choose from your other 2 options I need to understand a technical detail. I read about a 500 record limit with Filter, Group, etc. However I am not sure how it is applied. If I Filter a CDS entity with 10,000 records and it returns 300 records from the filter is this going to work. Or does it mean the filter will only be applied to the first 500 records of the 10,000 record dataset and maybe only return a 100 records?

 

Note: The page shown is just a test page. I have been building small test pages of each part of the overall design. This page is a bit of a dashboard, however other parts of app will be data entry, searches, triggers on certain events. Basically, we have 20 companies, each company has to sumbit a daily report. Part of this report is revenue and labour. So yes, one page would be dashbord of revenue from all devisions. My next problem to solve is to trigger an event if a report is not submitted on time. From what I have read Flow is the place for this.

mdevaney
Level 8

Re: Summing like series in a chart

To answer your question: “If I Filter a CDS entity with 10,000 records and it returns 300 records from the filter is this going to work.”, my response is Yes. FILTER will look at all 10,000 records and output up to 500 records that match the criteria. This is because FILTER can be delegated. The general concept is here is when using a delegable function like FILTER all records in the database are evaluated. The number of records in the result set depends on where they are output to. For instance, a collection can store up to 500 records in memory, however a gallery can hold all of items because the data is streamed rather than stored. Hopefully this is not too much information all at once!

Once you are comfortable with PowerApps I would highly suggest looking into PowerBI. I have automated a large portion of my own reporting with Power BI and it’s query language: Power Query (it’s basically SQL with a GUI interface). If you just want to learn the query language pick up the book called “M is For Data Monkey” by Ken Puls. It’s an Excel book but it shows how to use Power Query for Excel and Power BI. I spent a weekend doing all the exercises and it was one of the most high-value technical skills I have learned in my career. Imagine combining SQL and automation scripts in Excel. It’s soooo awesome!

And yes, I agree Flow is the right place for triggered events.

Let me know if I’ve answered you questions for now. I enjoy the conceptual questions and I’m always happy to help!
JasonF
Level: Powered On

Re: Summing like series in a chart

Let me guess,

If you want to return data sets larger than 500 records PowerBI is the way to go?

 

JasonF

JasonF
Level: Powered On

Re: Summing like series in a chart

I decided to use your first solution

I created a Collection

Collect(CompanyNameCollect,'Test Sales3S')

THen created a new column filling it with the look value from the master table:

ClearCollect( CompanyCollect, AddColumns(CompanyNameCollect, "Company", crdf5_DivisionName.'Division Name'))

Then Grouped the sales by Company (Bar Chart):

ClearCollect( SalesByCompany, GroupBy(CompanyCollect,"Company","Company Sales"))

Then Sum'd the sales by company:

ClearCollect( SalesByCompanySum,AddColumns( SalesByCompany, "Total Sales", Sum('Company Sales',Sales)))

 

Like wise I can group the sales by type (Pie Chart):

ClearCollect( SalesBytypeSum,AddColumns( ClearCollect( SalesByType, GroupBy(CompanyCollect,"crdf5_salestype","Company Sales Type")), "Total Sales", Sum('Company Sales Type',Sales)))

 

And you can get this:

Charting.JPG

Helpful resources

Announcements
thirdimage

Power Automate 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

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 308 members 5,581 guests
Please welcome our newest community members: