Data Relationship between orders and sales targets

[nhol]

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 by SalesRep 

2) Targets - quarterly target by SalesRep

I'm trying to present a very simple grid that shows by quarter the total orders for that quarter vs. target per SalesRep.

For the whole year of 2017 it shows correctly the Total Orders vs. the sum of 4 quarters per SalesRep.

However, when I'm trying to add the quarterly field (aka Q1 2017 , Q2 2017 etc.) either from the Target table or from the Order table... this is where it fails to show it logically:

1) when adding the QtrYear field from the Target's table, then TotalOrders (for the same period) is repeated over and over 4 times

2) when adding the QtrYear field from the Order's table, then Targets (for the same period) is repeated over and over 4 times

Here is what I'm getting:

Any help would be highly appreciated!

Thanks!

NH

[cchavez]

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

[Anonymous]

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

[nhol]

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]

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]

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   

[v-ljerr-msft · Accepted solution]

Hi @nhol,

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

Regards