Actual vs Target Modeling Issue

[Tooks9597]

Hello,

I'm trying to build a visual which shows me my Actual vs Target. I need this broken down for all of our store locations. I created the Data Model, but for some reason, the visual shows the same value for either my Actual or my Target. And when I attempt to use my Location field in my DimLocation, the visual breaks.

Right now, my target is showing correctly, but my Amount Actual is displaying the same thing for each store location. I am pulling data from a Dataverse table: Which is "FactPayments" in my Data Model.

I am then pulling data from a Sharepoint List, and in my Data Model it is called: FactTargetPurch

My goal is to have a manager fill in a Target spending goal for all locations for the following month. I then want this goal to pull into my PBI visual as my Target.

I have another Dimensional table titled DimLocation:

And I have a Date/Calendar Dimensional table titled: "Dates table": 

Here is a screenshot of my Data Model: 

Dates table relationships:

Date - Month (FactTargetPurch)

Date - Post Date (FactPayments)

DimLocation relationships:

Location - Location (FactTargetPurch)

Location - Location (FactPayments)

DimVendor relationships:

Pay To - Pay To

If someone could please look at this and explain to me what is going wrong that would be greatly appreciated. I've been attempting to solve this for the past 3 days and haven't came across a fix online or with ChatGPT. 

Thank you,

[danextian]

Hi @Tooks9597 ,

The columns that have a relationship should match with each  other. You're using Dates[Date] to Target[Month] which doesn't make sense as the latter only has the end of months. Instead, use Mth Yr and make sure the relationship flows in one direction from Dates to Target. This will be likely many to one or many to many

[Tooks9597]

Hi danextian,

My Dates[Date] is in the format of mm/dd/yyyy, my Month column in my target is also in that same format. 

[danextian]

Can you please post a sanitized copy of your model/ confidential data removed?

[Wilson_]

Hi Tooks9597,

It looks like you're using Location from your fact table in your Small Multiples. Does it do what you want if you use the Location from your dimension table instead?

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[Tooks9597]

Hi Wilson, 

When I switch my Small Multiples to my Location from my DimLocation table, it breaks the visual it says "Couldn't load data for this visual"

[Wilson_]

Tooks,

I get errors to your pbix because I don't have permissions to some of your tables. However, your issue in using the Location from the dimension table likely stems from the fact that this is a mxed storage mode model, with DirectQuery tables, Imported tables and a calculated table pointing at one of your DirectQuery tables.

This maaaaaay be a decent place to start digging. Unfortunately, I don't work with them enough to help you here.

[Wilson_]

Tooks,

Ah you said that. Can you please share a sample pbix file? (If you don't know how, please check the pinned thread in the forum.) It would make debugging your issue easier. 🙂