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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nhol
Advocate II
Advocate II

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

 

Capture2.PNG

Any help would be highly appreciated!

 

Thanks!

NH

1 ACCEPTED SOLUTION

Hi @nhol,

 

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

 

Regards

View solution in original post

6 REPLIES 6
cchavez
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 

 

Relaciones.JPG

 

 

Relaciones2. JPG.JPG

 

 

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

 

Finish.JPG

 

I made a sample file for you

 

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

 

Hope, works for you

 

Best Regards

 

Carlos

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

 

Capture3.PNG

cchavez
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

 

Regards

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.