In this Power BI Tutorial, we will discuss how to add a calculated column in the PowerBI desktop. We will also see how to connect the SharePoint List to Power BI desktop.
Power BI Calculated Column
Calculated Column is a custom column that we can add manually in Power BI. In Power BI we can add the mathematical expression in a calculated column So that the new column will calculate the value by using the expression and the result will be applicable for all the rows.
From the below example we will see how to add the Calculated Column.
I have created a SharePoint List having below columns:
- EmployeeName
- PrincipelAmount
- TimePeriod(Month)
- RateOfIntrest
Open the Power BI desktop in the local system. Give the office 365 credentials if asked. Click on GetData -> More.
In the Get Data Page click on “Online Services” -> “SharePoint Online List”. Click on Connect.
Pass the Site URL in the text box and click on OK.
Select the List name in the Navigator page and click on Load.
In the Data tab delete the unwanted column. To delete the column right-click on the column name you will get the delete option.
Add New Calculated Column in Power BI Desktop
Microsoft Power BI provides to add a new column in the Power BI desktop.
We can add custom Columns in two ways. One way is from the Data tab. Right, Click on the Column Name. Click on “New Column”.
Another one is from the Report tab. Right-click on any Column Name. We will get the “New column” option.
After adding the New Column I have added the rule like below:
SimpleIntrest = (EmployeeAccountDetails[PrincipelAmount]*EmployeeAccountDetails[TimePeriod(Month)]*EmployeeAccountDetails[RateOfIntrest])/100
Then automatically the rule will calculate the simple interest for all the row.
For calculating the total amount I have added another table and add the rule:
Total Amount = EmployeeAccountDetails[PrincipelAmount]+EmployeeAccountDetails[SimpleIntrest]
In the Report tab, we will get the custom column name which we have created. I have added one Visualization from the Visualizations list. In the Axis and Value field, I have added the field name. One I have added “EmployeeName” and another one is “TotalAmount”.
I have added one more table visualization and added all the column names.
You may like following Power BI tutorials:
- How to change data source in Power Bi
- Power BI Admin Portal
- Power BI Quick Insights
- Power BI Group By Examples
- Power bi shared dataset permissions management
- Scheduled Power BI report data refresh
- Power BI nan error (Not a number) while dividing by Zero
- Power BI report using People Picker Field
- Power BI Measure Sum and Subtract Example
- Data Labels in Power BI
- Power bi table visualization
- Subtraction in Power bi using DAX
- Get Month Name from Month Number in Power BI
- Power BI convert yyyymmdd to date
- Power BI convert hours to minutes
We saw how to add the custom column and a calculated column in Power BI. How to add the rule in a custom column? We also saw how to display the custom column data in the Power BI visualization.
Hello Everyone!! I am Bhawana a SharePoint MVP and having about 10+ years of SharePoint experience as well as in .Net technologies. I have worked in all the versions of SharePoint from wss to Office 365. I have good exposure in Customization and Migration using Nintex, Metalogix tools. Now exploring more in SharePoint 2016 🙂 Hope here I can contribute and share my knowledge to the fullest. As I believe “There is no wealth like knowledge and no poverty like ignorance”