Power BI – Sales and Expense Report – Negative Numbers with DAX CONTAINSSTRING

When looking at sales income versus expense values in Microsoft Power BI, you can easily turn expense / cost values into negative numbers using DAX formulas. I’ll demonstrate a scenario using CONTAINSSTRING 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 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:

SalesReportScreenshot

Thanks for reading.

Advertisements


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

Tags: , , ,

1 reply

Trackbacks

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: