Sales vs Target Matrix Comparison Issues

Power BI Data Model
1

Greetings all,

I’m looking to get advice regarding a historical sales comparison. I have two tables, actual invoice sales and target invoice sales. The issue is that my target sales are broken down by monthly targets instead of having a daily value to compare to my invoice sales table.

My sales manager wants to see a side by side (same visual) view and at the moment I can’t seem to figure out how to compare the two figures within a single chart.

My target sales table can be linked back to actual sales by an identifier Territory_ID.

I’ve created a Qtr/Month/Year key in my Sales Targets table tied back to the Dates table with the same key, which allows me to use this on a high-level scale.

Here’s the structure of the table:

Regions_Targets
Regions_Targets.PNG749×258 14.4 KB

Now when I compare it on a matrix to look at actual vs targets, the result doesn’t provide targets for years other than 2018.

Sales%20vs%20Targets%20Comparison

Any help would be greatly appreciated on how to move forward from here.

Thanks,

Nic Nouchi

2

Hi @nnouchi,

By what you are saying, seems like your problem is in the relationships, have you linked your date table with the Target table? Because I am seeing your are doing the relationship with your sales table instead of the date table.

Can you please show me how are you setting up your model with your relationships? Or if you can share with me a dummy pbix file to see what are you working with to support you in a more efficient way.

Best regards,

Jorge Galindo

3

Hi Jorge,

I do have it linked, it’s being linked by the inactive relationship. My target sales formula is the following:

Target Sales = CALCULATE(Sum(RM_Target[Target_amount]),USERELATIONSHIP(RM_Target[QTR/MTH/YR],Dates_V2[QTR/MNTH/YR]))

I have the invoice_date within my invoices table as the primary date that is being referenced in my relationships.

I’ll post the pbix file right now.

5

@JAPG I’ve attached the pbix

Sales vs Target.pbix (3.0 MB)

6

Hi @nnouchi,

First of all, I see that your model and your measures are not following the best practices Enterprise DNA recommend. I would recommend you going through this course:

I have seen your model and from what I get from the picture and the model is that in the picture, in your Columns RMD_Targets_Name_ID you got a blank value, and some invoices got that Region, that is why you are getting all the blank values there in the first two columns.

And for the Central Region and so on, check your data, probably there is no data for Target sales for those regions.

Best regards,

Jorge Galindo