Forum Discussion

paris's avatar
paris
Helper V
7 years ago
Solved

Relationship problem

Hi all,

 

I have forecast and actual data in diffenet format and want to combine in report format. 

I can not change forecast from monthly basis to daily basis. Could you please help me to set up relationship?

 

 

All the best,

LoveParis

Need Help
  • paris's avatar
    paris
    7 years ago

    Hi Zoe,

     

    Thank you for sending me the demo file.

     

    1) Is there any reason actual is under calender?

    2) I use slicer to select by country or city. 

    3) However..   I set up relationships (same as yours) but it seems not working. I got total value (sum of paris, london, tokyo) for each day. Is there any possible reason causing this problem? 

     

    All the best,

    LoveParis

     

5 Replies

  • dax's avatar
    dax
    Community Support
    7 years ago

    Hi paris,

    You need three tables, Actual, Foreaast and calendar, then you could create a column in actual table like below

    Column = SWITCH(Actual[city],"Tokyo","Japan", "Japan","Japan", "London","UK", "Paris", "France")

    create the relationship like below

    Then create measures like below

    actual =
CALCULATE (
    SUM ( Actual[productions] ),
    FILTER ( ALL ( Actual ), Actual[date] = MIN ( 'calendar'[Date] ) )
)

    forcast =
CALCULATE (
    SUM ( Forcast[production] ),
    FILTER (
        ALLEXCEPT ( Forcast, Forcast[Country] ),
        MONTH ( Forcast[date] ) = MONTH ( MIN ( 'calendar'[Date] ) )
    )
)

    Then you could create table like below

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • paris's avatar
      paris
      Helper V
      7 years ago

      Hi Zoe,

       

      Thank you very much for your reply!:smileyhappy:

       

      1) I do not need to build relationship between calender and forecast/actual?

      2) People keep adding new cities. Instead of using switch function, can I creat a new table country/city and build relationship with forecast/actual? 

       

      All the best,

      LoveParis

      • dax's avatar
        dax
        Community Support
        7 years ago

        Hi paris, 

        Yes, you don't need to create relationship between calendar and actual,. And if you want to use another country table, you could create relationship like below

        You could refer to my pbix file for more details.

        Best Regards,
        Zoe Zhi

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.