Power BI DAX CONTAINSSTRING – Sales and Expense Report

Power BI DAX CONTAINSSTRING in an Expense Report

When looking at sales income versus expense values in Microsoft Power BI, you can easily turn expense / cost values into negative numbers using the Power BI DAX CONTAINSSTRING formula. I’ll demonstrate a scenario using this formula to convert numerical values imported from Microsoft Excel from positive to negative.

You should read through the previous post which will bring you to the current starting point in this blog. That is, with a single table joined together using multiple tables from Excel by way of the Append Queries function in Power Query Editor.


This main Power BI visual in this report is not intended to show a total sum of costs minus expenses. It’s a visual report for viewing costs and expenses in their respective categories across cities and months. To those ends, I want to change cost values to negative numbers so that they appear accordingly on the report, and I’ll do that with the CONTAINSSTRING and IF DAX functions.

  • Open Power BI Desktop
  • Go to the Modeling pane and find your FinalSalesFigures table
  • Open the Modeling Tab
  • Click the New Column Button
  • Add a new column using the following formula
IF(CONTAINSSTRING(FinalSalesFigures[Category],"Cost"),FinalSalesFigures[Value] * -1,FinalSalesFigures[Value])

Semantically, the formula reads: If the Category field contains the word Cost, take the “Value” amount, multiply it by -1, and persist that value to the new column. That is the negative dollar amount in cost. Otherwise, take the current value…a positive amount in sales dollars.

Here’s a walk through:


Clustered Column Chart and Slicers

The report needs to enable business users to work through a visual of sales income versus sales expenses. This will be done with a clustered column chart. Also, they want to be able to slice data by the city and month, so I’ll add two slicer controls for that function.

Finally, though it’s not the main purpose of the report, the team does need to see net profits. That can be handled easily with a table control given that Power BI will default the new ActualValue column to be aggregated as a sum.

Here’s that walk through:


I didn’t spend too much time cleaning everything up for the sake of the video, but of course, the final report can be published to Office 365 using Power BI Services and made available to your entire team. The final product would look more like this:

Power BI DAXCONTAINSSTRING final dashboard.

Thanks for reading.

Categories: Microsoft Excel, Office 365 and O365, Power BI, SharePoint

Tags: , , ,

1 reply


  1. Power BI – Sales and Expense Reports – Prepare Excel Tables from Office 365 SharePoint – Westmorr Consulting

Leave a Reply