Goals Table Best Practice

Question

Hi All, I have monthly goals for companies and a table that contains actual sales. What would be the easiest way to create a relationship between the 2? i initially tried to create a goals table, is this the best practice? I'd like to have a graph of actual sales, goals, in year to date format. I am having trouble building the relationship, is it because of my date?

greg_deckler · Answer

Tough to say,&nbsp;Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
&nbsp;
Is your goal data momthly, quarterly, yearly, ? Often I have seen the pattern of creating a column in your fact table based on date that specifies the month, quarter or year it is in based on what you are trying to match up with. For example, create a quarter column (or year/quarter column like 202001) if your goal data is quarterly. Do the same thing in your other table. Create a relationship.

anonymous · Answer

Hi,&nbsp;If you have a Date table. Create a relationship between Date table to actual sales table and Create a relationship between Date table to goals table.&nbsp;Use column from Date table on the x-axis.&nbsp;&nbsp;Did I answer your question? If Yes, Give me a Kudo&nbsp;

v-yuta-msft · Answer

Anonymous&nbsp;,
&nbsp;
About how to create and manage table relationships in power bi, I would suggest you refer to docs and blog below:
https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships
https://docs.microsoft.com/en-us/power-bi/desktop-relationships-understand
https://radacad.com/what-is-the-direction-of-relationship-in-power-bi
&nbsp;
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

pauldbrown · Answer

Anonymous&nbsp;
A key issue when setting up the model is to understand the level of granularity of your sales and goals data. Take your goals table for example. If your sales data's granularity is equivalent (date, company) you can merge the tables into a single table if you wish.
If however your sales table has more granularity (product for example), your goals table as is does not have a product breakdown. it's best to keep the sales and goals table separate and use lookup/dim tables with common fields (date table, company table...) to use in your DAX &nbsp;statements, filters, slicers etc...(and/or calculate the goals down to product level by using a relevant parameter to do so - previous year's weight or whatever).
I actually prefer to keep the tables separate.