Jul 19 2022 06:27 AM - edited Jul 19 2022 06:29 AM
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?
Jul 19 2022 09:29 AM
Jul 20 2022 08:00 AM - edited Jul 20 2022 08:07 AM
Solution@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)
Jul 20 2022 08:00 AM - edited Jul 20 2022 08:07 AM
Solution@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)