Forum Discussion

cocomy's avatar
cocomy
Resolver I
8 years ago
Solved

Merge queries in different format

Hi

 

I want to merge tables in different format.

I tried query,  naturalleftouterjoin etc but I can not build relationships to work .

When I try to comvert from A to C, duplication occurs. Can you suggest how should I do it to achieve Table E in the end?

 

  • Table A - daily forecast by month by branch
  • Table B- calender (to convert from monthly to daily)
  • Table C = Table A + B , daily forecast by day by branch
  • Table D - actual daily sales by branch *if no sales, no record in the table
  • Table E = Table C+D , actual daily sales by branch with daily forecast

 

 

 

 

 

 

 

Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    Hi cocomy,

     

    I was abble to replicate your information follow the steps below:

     

    • Create a new table Branchd

       Branch   ID

    Tokyo1
    Osaka2
    Nagoya3

     

    • Related the following tables:
      • Branch - Daily Sales : relate by branch
      • Branch - Daily forecast: relate by branch
      • Calendar - Daily Sales: relate by Date
    • Create the measure below:

     

    Measure created on Daily Forecast Table

Daily Forecast Total =
VAR Select_Month =
    MAX ( Calendar[Monthly Date] )
RETURN
    CALCULATE (
        SUM ( 'Daily Forecast'[Daily Forecast] );
        'Daily Forecast'[Monthly Date] = Select_Month
    )


Measure created on Daily Sales Table

Actual totals = SUM('Daily Sales by branch'[Actual])

     

    • Make your table visual with the following columns / measures:
      • Date (Calendar Table)
      • Branch (Branch Table)
      • Actuals totals (measure)
      • Daily Forecast Total (measure)

     

    As you can see the result is what you need to have, I have calculated measure because if you need you can calculate differences and percentages over objective.

     

     

    Here is a link to PBIX file with the examples and measures.

     

    Regards,

    MFelix

     

  • cocomy's avatar
    cocomy
    8 years ago

    Thank you very much for detailed explanation. It is what I have been looking for ages.

     

    All the best,

    coco

2 Replies

  • MFelix's avatar
    MFelix
    Super User
    8 years ago

    Hi cocomy,

     

    I was abble to replicate your information follow the steps below:

     

    • Create a new table Branchd

       Branch   ID

    Tokyo1
    Osaka2
    Nagoya3

     

    • Related the following tables:
      • Branch - Daily Sales : relate by branch
      • Branch - Daily forecast: relate by branch
      • Calendar - Daily Sales: relate by Date
    • Create the measure below:

     

    Measure created on Daily Forecast Table

Daily Forecast Total =
VAR Select_Month =
    MAX ( Calendar[Monthly Date] )
RETURN
    CALCULATE (
        SUM ( 'Daily Forecast'[Daily Forecast] );
        'Daily Forecast'[Monthly Date] = Select_Month
    )


Measure created on Daily Sales Table

Actual totals = SUM('Daily Sales by branch'[Actual])

     

    • Make your table visual with the following columns / measures:
      • Date (Calendar Table)
      • Branch (Branch Table)
      • Actuals totals (measure)
      • Daily Forecast Total (measure)

     

    As you can see the result is what you need to have, I have calculated measure because if you need you can calculate differences and percentages over objective.

     

     

    Here is a link to PBIX file with the examples and measures.

     

    Regards,

    MFelix

     

    • cocomy's avatar
      cocomy
      Resolver I
      8 years ago

      Thank you very much for detailed explanation. It is what I have been looking for ages.

       

      All the best,

      coco