cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Charles-v-D
Level: Powered On

building a management report from SharePoint

Hi All,

I'm trying to build a PowerApps app that shows a management report via tables and graphs. My data sources is SharePoint List which is shown below. I like to make a PowerApps app that now does 2 things:

  1. gives table that shows total $ per month-year
  2. gives bar graph that shows same

I have added a mockup of what I like to see (2nd picture)

When I add table or graph in PowerApps I only see direct representation of my sharePoint List and can't find settings to build totals etc.

 

Any feedback is greatly appreciated!

Regards

Charles

 

d1.JPGthe SharePoint list

 

 

 

 

 

 

 

 

 

d4.JPGthe desired management table

 

 

 

 

 

 

 

 

d3.JPGcorresponding management graph

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-siky-msft
Level 8

Re: building a management report from SharePoint

Hi @Charles-v-D ,

 

The Groupby function can be convenient to nest the table,  we can group the value from the same preiod and easily calculate the sum.

The following formuals are for your reference.

1. create a "period" button , set Onselect as below to create a "period" column and collect in a collection. 

ClearCollect(colmoney, ShowColumns(AddColumns(Money,"period",year&"-"&month),"value","period"))

2. create a "group" button, set OnSelect as below to group the value.

ClearCollect(Coltotal,GroupBy(colmoney,"period","total"))

3. create a "sum" button, set OnSelect as below to calculate the sum

ClearCollect(ColSum,AddColumns(Coltotal,"Sum",Sum(total,value)))

4. create the column chart, set Items as ColSum below to show the Sum.

Capture.PNG

 

Best regards,

Sik

2 REPLIES 2
Super User
Super User

Re: building a management report from SharePoint

The bar graph is pretty simple. You need to provide the graph the data in the desired management table. You could use a combination of the GroupBy(), AddColumns() and Sum() functions to create the data table. The link below gives an example of aggregating data.

 

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-groupby#aggregate-re...



--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.
v-siky-msft
Level 8

Re: building a management report from SharePoint

Hi @Charles-v-D ,

 

The Groupby function can be convenient to nest the table,  we can group the value from the same preiod and easily calculate the sum.

The following formuals are for your reference.

1. create a "period" button , set Onselect as below to create a "period" column and collect in a collection. 

ClearCollect(colmoney, ShowColumns(AddColumns(Money,"period",year&"-"&month),"value","period"))

2. create a "group" button, set OnSelect as below to group the value.

ClearCollect(Coltotal,GroupBy(colmoney,"period","total"))

3. create a "sum" button, set OnSelect as below to calculate the sum

ClearCollect(ColSum,AddColumns(Coltotal,"Sum",Sum(total,value)))

4. create the column chart, set Items as ColSum below to show the Sum.

Capture.PNG

 

Best regards,

Sik

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

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