Forum Discussion

lancealcabasa's avatar
lancealcabasa
New Member
9 years ago
Solved

Help creating a 'delta' chart

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

  • 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

1 Reply

  • Reid_Havens's avatar
    Reid_Havens
    Most Valuable Professional

    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