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 column that always has the value 30 in it. The idea is that i multiply the days by 30 to get a total value. This all works.
I have 2 issues that i feel are related, one simple and one more complicated (i think).
The first:
I am wanting to have a total under the "amount to pay" column which holds the days * 30 - there is no option to add a total.
Second:
I am wanting to use power BI to create some dashboards for this data but there is no option to sum the values that have been created for "total to pay".
It seems that this calculated value is not seen as a number that can have maths done to it.
My question:
How can use a column with a calculated number to get totals and use in power BI to graph?
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.
