Forum Discussion

nhol's avatar
nhol
Icon for Advocate II rankAdvocate II
8 years ago
Solved

Data Relationship between orders and sales targets

Hi,   I know this might be an easy question for many people in the community but I'm still struggeling with this one. I have two tables: 1) Orders - actual orders by customers in $$$ by date and ...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi nhol,

     

    Great to hear the problem got resolved! Could you accept the helpful reply as solution to close this thread? :smileyhappy:

     

    Regards

cchavez's avatar
cchavez
Frequent Visitor
8 years ago

NH the problem is that you have not created relationships between both tables, so the logic of time intelligence does not work.

 

I suggest you independently manage a calendar table. In the same way, very few companies work their objectives per quarter, you will have easier administration of dates in the way that I am suggesting you work

 

 

 

 


 

 

 

The, you can create the relationships 

 

 

 

 

 

Then you can play with dates, salesrep, periods, etc...

 

 

I made a sample file for you

 

https://1drv.ms/x/s!AhUoMuA_MpPugQN568OHGTidYbjK

 

Hope, works for you

 

Best Regards

 

Carlos

  • nhol's avatar
    nhol
    Icon for Advocate II rankAdvocate II
    8 years ago

    Thank you Carlos for the in depth answer and solution you provided.

    I maintain also a date schedule by day, month, quarter, year and any other date combination format, however when I was trying to connect this table to the Order table the connection can NOT be established. So I went and did some reverse engineering by adding a unique ID for each target line that include a concatination of SalesRepID_QX2017. Did the same in the order table based on the actual Order Date. This seemed to workout but it couldn't be activated and I don't know why. Any other relationship between the tables ended up with an error message that "... one of the columns must have unique values".

     

    below is the star scheme I have in place today.

     

    Thansk again!!

     

    • cchavez's avatar
      cchavez
      Frequent Visitor
      8 years ago

      NH, you MUST HAVE a calendar table, is the cornerstone of all transactional models.

       

      The FACTS tables always have a date, and you can have Many FACTS tables like, sales, Inventory, Credit Notes, etc... the only way to related each other, is the calendar Table (unique calendar table). After you can explor a DAX named USERREATIONSHIP... this help you tu use many date like, invoice dates, delivery dates, pay dates, etc...

       

      Your model will work, if you make a few changes:

       

       

      you should not relate the table of "clients" with that of "salesrep", because even if a seller is the "owner" of a client, in real life, more than one salesrep, may invoice a specific client.

      • nhol's avatar
        nhol
        Icon for Advocate II rankAdvocate II
        8 years ago

        Thank you so much Carlos!!!

        Finally I got it to work following your specific instructions on how I should design the structure correctly.

        I still had some issues with empty rows that for some reason PowerBI doesn't know to ignore those although they are completly blank in the Excel sheet, but once I realized that this is the reason for PowerBI to keep throwing an error (even after I mimic your structure) I was able to get it to work.

         

        Thanks again for your assitance!

         

        Best Regards,

        Nir   :smileywink:

         

         

         

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Three days of searching and this post proved to be Gold!
    Thank you cchavez ! Great answer and the images with the schema very useful!