Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Advocate II
Advocate II

Data Relationship between orders and sales targets



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!





Hi @nhol,


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



View solution in original post

Frequent Visitor

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



Sales & Target Tables.JPG



Calendar Table.JPG




The, you can create the relationships 





Relaciones2. JPG.JPG



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




I made a sample file for you!AhUoMuA_MpPugQN568OHGTidYbjK


Hope, works for you


Best Regards



Not applicable

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

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!!



Frequent Visitor

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:


fixed model.JPG


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.

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   Smiley Wink






Hi @nhol,


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



Helpful resources

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors