Forum Discussion
SP Lists Monthly Total
Using SP Online Lists I have a simple purchase list that details purchases by date, e.g.;
+----------+-------------+-------+
| date | description | price |
+----------+-------------+-------+
| 11/05/22 | item 1 | 9.99 |
+----------+-------------+-------+
| 15/05/22 | item x | 15.99 |
+----------+-------------+-------+
| 19/07/22 | item z | 7.50 |
+----------+-------------+-------+
I would like another row that displays the totals by month. e.g.
+------------+-------------+-------+
| date | description | price |
+------------+-------------+-------+
| 11/05/22 | item 1 | 9.99 |
+------------+-------------+-------+
| 15/05/22 | item x | 15.99 |
+------------+-------------+-------+
| TOTAL May | | 25.98 |
+------------+-------------+-------+
| 19/07/22 | item z | 7.50 |
+------------+-------------+-------+
| TOTAL July | | 7.50 |
+------------+-------------+-------+
The list will be populated on an ongoing basis, the monthly totals need to be visible permanently, as new rows are added to months the totals should update accordingly. I'm comfortable working with calculated fields.
How can I achieve this?
jonboylib whilst I agree with Bharath Arja about Power BI, it comes with issues around distributing reports because all users need a Power BI Pro licence to view a report.
But a simple solution and keepng it all in SharePoint is to have a calculated column for the Month, in my example the formula is =TEXT([Insurance Expiry Date],"MMM YYYY").
Then create a new view of the list and group by that month column and set the total to sum the amount column. It will always update as new items are added to the list.
Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)
2 Replies
jonboylib whilst I agree with Bharath Arja about Power BI, it comes with issues around distributing reports because all users need a Power BI Pro licence to view a report.
But a simple solution and keepng it all in SharePoint is to have a calculated column for the Month, in my example the formula is =TEXT([Insurance Expiry Date],"MMM YYYY").
Then create a new view of the list and group by that month column and set the total to sum the amount column. It will always update as new items are added to the list.
Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)- Calculated columns may not be relevant for what you are looking to accomplish.
Are you licensed to use PowerBI? Did you check the out of the box visualization of your List that is available in Power BI. Go to your list -> In top ribbon select Integrate drop down -> PowerBI -> Visualize the list.
Keeping data in your list and creating totals or reports in PowerBI works out better in most of the scenarios.
