Forum Discussion
Using calculated columns in sharepoint list in BI
Hi All, I have a list on sharepoint where i have a start date and an end date, i am using a calculated column to work out how many working days are between the two dates. I then have a hidden col...
Hi Adamhumbug
To achieve the desired results with calculated columns in SharePoint and use them in Power BI, you can follow these steps:
Fixing the Total Calculation Issue: As you've mentioned, there is no option to directly add a total to a calculated column in SharePoint. However, you can work around this by using a view with a Group By setting.
- Create a new view in your SharePoint list.
- In the view settings, choose "Group By" the "Amount to Pay" column.
- Select "Sum" as the option for the "Days * 30" column in the Group By settings.
This will create a grouped view that shows the total amount to pay based on the calculated column. It's important to note that this view will only be available in SharePoint and won't affect the underlying data.
Using the Calculated Column in Power BI: To use the calculated column in Power BI, you'll need to connect Power BI to your SharePoint list as a data source.
- Open Power BI and click on "Get Data."
- Choose "SharePoint Online List" as the data source and connect to your SharePoint site.
- Select the list that contains the calculated column with "Days * 30."
- Click "Load" to import the data into Power BI.
After importing the data, the "Days * 30" column should be available in the data model. Now, you can use this column in Power BI to create graphs and dashboards.
- Drag the "Days * 30" column into the Values field well on the visualization pane.
- Choose the appropriate chart type (e.g., bar chart, line chart) to display the total amount to pay.
Power BI will perform the calculations based on the data from the SharePoint list and show you the total amount to pay.
By following these steps, you should be able to get the total under the "Amount to Pay" column in SharePoint and use the calculated column in Power BI to create graphs and dashboards based on the data.
Lalit Mohan I am afraid neither of these approaches worked.
When setting up a new view in Sharepoint there was no option to sum the values when grouped and when importing the data into power BI there was still no option to sum the Amount Due data.
I just get the option to count it.
- Make sure you set any number columns to number type in the query editor - it may be defaulting to text type. Anything it doesn't recognize as a number it will try to count instead of sum.
- I can't get the Total Calculation to work either. In the example above, the "Days * 30" column is still calculated. I can go to Group By settings to group by it (or anything else) but I still need to go to Totals settings to select the Sum option. All the columns that contain text only have the option to Count. The number fields have Count, Average, Max, Sum, etc, but the required column name is not listed at all (I assume, because it's a calculated column).
Can you please provide more detailed explanation of where I find the Group by settings that allow me to sum the required column.
