locked
Help creating a 'delta' chart RRS feed

  • Question

  • 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:

     salestable.JPG

     

     

    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"

     

    boughtmonth.JPG

     

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

     

    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):

     

    deltamonth.JPG

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

     

    deltatable.JPG

     

    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

    Friday, May 26, 2017 5:43 AM

Answers

  • Hi OCS.New,

    Thanks for your question.

    In this scenario, you may need to create 3 date table, BoughtDate,SoldDate and Date use below formula:
    BoughtDate = CALENDAR (DATE (2017, 1, 1), DATE (2017, 12, 31))
    SoldDate = CALENDAR (DATE (2017, 1, 1), DATE (2017, 12, 31))
    Date = CALENDAR (DATE (2017, 1, 1), DATE (2017, 12, 31))

    Then build relationships as below image,please set relationship between BoughtDate[Date] and Date[Date] to inactive, and set all the cross filter direction to both direction :


    Create measure Sale on table SoldDate as below:
    Sale = CALCULATE(SUM(Sales[Sold]),FILTER(Sales,Sales[SoldFlag]="YES"))

    Create measure Bought on table BoughtDate as below:
    Bought = 0-CALCULATE(SUM(Sales[Buy]),FILTER(Sales,Sales[SoldFlag]="NO" ))


    Create measure Bo on table Date as below:
    Bo = CALCULATE([Bought],USERELATIONSHIP(BoughtDate[Date],'Date'[Date]))

    Create measure Sal on table Date as below:
    Sal = [Sale]

    Create measure FinalMeasure on table Date as below:
    FinalMeasure = [Sal]+[Bo]

    Add a calculated column YearMonth on table Date as below:
    YearMonth = year('Date'[Date])*100+MONTH('Date'[Date])

    In the end, you can use visual Matrix to display the YearMonth and FinalMeasure, put YearMonth on column and FinalMeasure on values.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Friday, May 26, 2017 9:42 AM

All replies

  • Hi OCS.New,

    Thanks for your question.

    In this scenario, you may need to create 3 date table, BoughtDate,SoldDate and Date use below formula:
    BoughtDate = CALENDAR (DATE (2017, 1, 1), DATE (2017, 12, 31))
    SoldDate = CALENDAR (DATE (2017, 1, 1), DATE (2017, 12, 31))
    Date = CALENDAR (DATE (2017, 1, 1), DATE (2017, 12, 31))

    Then build relationships as below image,please set relationship between BoughtDate[Date] and Date[Date] to inactive, and set all the cross filter direction to both direction :


    Create measure Sale on table SoldDate as below:
    Sale = CALCULATE(SUM(Sales[Sold]),FILTER(Sales,Sales[SoldFlag]="YES"))

    Create measure Bought on table BoughtDate as below:
    Bought = 0-CALCULATE(SUM(Sales[Buy]),FILTER(Sales,Sales[SoldFlag]="NO" ))


    Create measure Bo on table Date as below:
    Bo = CALCULATE([Bought],USERELATIONSHIP(BoughtDate[Date],'Date'[Date]))

    Create measure Sal on table Date as below:
    Sal = [Sale]

    Create measure FinalMeasure on table Date as below:
    FinalMeasure = [Sal]+[Bo]

    Add a calculated column YearMonth on table Date as below:
    YearMonth = year('Date'[Date])*100+MONTH('Date'[Date])

    In the end, you can use visual Matrix to display the YearMonth and FinalMeasure, put YearMonth on column and FinalMeasure on values.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Friday, May 26, 2017 9:42 AM
  • Hi OCS.new,

    did the suggested solution work for you? If so, please mark it as answer so that others can benefit from it as well. Thx!


    Imke Feldmann
    MVP Data Platform
    TheBIccountant.com

    Sunday, June 25, 2017 6:52 AM
    Answerer