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.

Using Power BI DAX CONTAINSSTRING

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:

Thanks for reading.

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

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