Forum Discussion
Actual vs Target Modeling Issue
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,
7 Replies
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?)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"
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. 🙂
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
