Greater Houston Texas Power BI Users

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

How to join a table with actual sale dates and units with a target table with only months and targets?

  • 1.  How to join a table with actual sale dates and units with a target table with only months and targets?

    Posted Feb 08, 2021 04:47 PM
    I have a table that shows actual units sold. These are the columns:

    Branch Product Date units Month

    I have another table that shows the target for each branch for each product for each month. Columns are:

    Branch Month Product Goals

    I set up a relationship using Branch column.

    When I try to show the goals for each month, powerbi shows the sum of all goals. How can I show the total units sold in each month and also the goals for the same month. Then I will be able to calculate the percentage goals completed.

    Thanks

    ------------------------------
    Murat Yildirim
    77079
    ------------------------------


  • 2.  RE: How to join a table with actual sale dates and units with a target table with only months and targets?

    Top Contributor
    Posted Feb 08, 2021 05:57 PM
    Edited by Nicolas MENDEZ Feb 08, 2021 05:57 PM
    Hi @Murat Yildirim,

    You will not be able to solve you problem with 2 tables...

    You have to work  with 4 tables comming from the two that are already in your PBI.

    1 table "Actuals" Product/Date/Units
    1 table "Branch" Branch (NO DUPLICATES !!!)
    1 table "Goals" ​Branch/ProductGoals/Date (You have to rebuilt a valid date (datetime format MM/DD/YYYY) based on Month and year. If no day info available you can set it to 1
    and 1 "DateTable" Date/Year/Month (NO DUPLICATES !!!)

    Now you can link
    DateTable[Date] with Actuals[Date] and Goals[Date]
    And Banch[Branch] with Actuals[Branch] and Goals[Branch]

    Measures :
    Actuals = SUM(Actuals[units])
    Goals = SUM(Goals[ProductGoals])

    Hope this helps..

    Feedback welcome

    Enjoy PBI & take care

    ------------------------------
    Nicolas MENDEZ
    Data & BI consultant
    Becom Consulting
    ------------------------------



  • 3.  RE: How to join a table with actual sale dates and units with a target table with only months and targets?

    Posted Feb 09, 2021 08:49 PM
      |   view attached
    Hi Nicolas,

    Thank you for responding. I had already tried creating date table but hadn't tried creating branch table. It still doesn't solve my problem though. When I put all in one matrix, I see the same goals for each product. I also would like to show 0 if there is no goals or actuals for that product type. Any help will be appreciated.

    Thanks
    Murat

    ------------------------------
    Murat Yildirim
    77079
    ------------------------------



  • 4.  RE: How to join a table with actual sale dates and units with a target table with only months and targets?

    Posted Feb 10, 2021 12:19 AM
    Solved. Based on your recommendations, I created another table for Products. That worked.

    ------------------------------
    Murat Yildirim
    77079
    ------------------------------



  • 5.  RE: How to join a table with actual sale dates and units with a target table with only months and targets?

    Top Contributor
    Posted Feb 10, 2021 01:43 AM
    Edited by Nicolas MENDEZ Feb 10, 2021 01:43 AM
    Well done !! Nice to have helped you....

    ------------------------------
    Nicolas MENDEZ
    Data & BI consultant
    Becom Consulting
    ------------------------------