building a management report from SharePoint

[Charles-v-D]

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

the SharePoint list

the desired management table

corresponding management graph

[Jeff_Thorpe]

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

[v-siky-msft · Accepted solution]

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.

Best regards,

Sik