Help creating a 'delta' chart

[lancealcabasa]

Hello, I am new to Power BI and would like help creating a 'delta' chart based on the Bough / Sold amount for a sample Sales table. I have the following table below:

 

Based on the table above, I create 2 charts which is 1) Bought Amount Totals per Month and 2) Sold Amount Totals per Month.

Bought Total per Month which sums up the "Buy" column if "Sold = 'NO'" and "BoughtMonth = Month Criteria"

Sales Total per Month which sums up the "Sold" column if "Sold = 'YES'" and "Sold Month = Month Criteria"

I have already created the following above in Power BI. My data source is a SharePoint List, which is based on the Sales Table. Now, I would like to create a "Delta Table" showing the difference between the Sales and Bought Totals per month, something like this (which I created in Excel):

In Excel, I have done this easily because I was able to create manually table and just calculated the deducted values like so:

But in Power BI, I am having difficulty creating the table above. Please note that for reasons, the Sales Table cannot be re-structured anymore. I can add calculated columns and such but I can't remove any of the existing columns there.

Please help how I can do the delta table

[Reid_Havens · Accepted solution]

Hi there,

Happy to help. You should be able to write a DAX Measure to do a comparison of the two values and put that on a chart in Power BI Desktop by Month. Queries look something like below:

DAX Measure for Buy:

Bought =
CALCULATE (
    SUM ( [Buy] ),
    TableName[Sold] = "No",
    TableName[BoughtMonth] = "Month Criteria"
)

DAX Measure for Sell:

Sold =
CALCULATE (
    SUM ( [Sold] ),
    TableName[Sold] = "Yes",
    TableName[BoughtMonth] = "Month Criteria"
)

DAX Comparison Measure:

Delta Comparison = 
[Bought] - [Sold]

I'm not sure what your "Month Criteria" is so I put that in as a placeholder for whatever value would need to go there for your first two measures. Hope this helps!

Reid Havens - Principal Consultant

PowerPivotPro