In my career I’ve had the opportunity to work with a multitude of different programming languages and technologies, but one application I haven’t frequently crossed paths with is Power BI. However, I’ve been fortunate enough to have a need to dive in and learn more about the tool recently, and I came across an issue with a SharePoint Online Calculated Column that I wanted to share my findings on.
The summary of the challenge is I have a calculated column (Currency) that is a sum of 2 Currency fields in my SharePoint Online list. You can quickly recreate this scenario by creating a new list and using the “Travel requests” template.
After doing this, you’ll end up with a list that has an Estimated Airfare column and an Estimated Hotel column. Create a new Calculated column, formatted as Currency, called “Estimated Total” and set it to:
[Estimated airfare]+[Estimated hotel cost]
At this point you’re all set; you can create a new data source in Power BI where you connect to SharePoint Online and choose the list you just created. When adding visuals within a report that reference this table, you’ll have no problem summing the Estimated airfare and/or Estimate hotel cost columns, but you’ll notice when trying to do the same with “Estimated Total” that you can only get a count on the value, and not a sum.
How to Resolve:
Ultimately there are a couple ways to resolve the issue, which I’ll quickly cover here:
- Create a new Column in the Power BI data source that sums the 2 values together, just as the SharePoint Online list is doing:
DAX: EST = ‘Travel requests'[Estimated airfare] + ‘Travel requests'[Estimated hotel cost] - Go into the data source in Power BI, select the Estimated total column, and change its Data type from type ‘Text’ (this is the real culprit on why it can’t be summed since Power BI doesn’t recognize its value as a number – even if you chose Number instead of Currency when creating the column in SharePoint Online the result is still the same here) to ‘Decimal number’. You can then also change the Format to Currency
Regardless of which of the 2 options above you choose, you’ll end up with a column that can now be utilized to show a Sum value in your reports. As always, I hope this post was helpful, and feel free to let me know if you have any additional questions in the comments.
Cheers,
Matt
