Goals Table Best Practice

[Anonymous]

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?

[PaulDBrown]

@Anonymous 

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

[Anonymous]

Thanks all for the feedback, spent some time to outline what im seeing, please let me know if i need to add more details.

I have 3 tables, actual data (All-Unfiltered Data), date table (Dates), and goals table (Goals).

I only have goals defined for certain companies which is why you see there are more actual companies than goals.  

I only want to track the YTD of the companies that have a defined goal, however the YTD is calculating companies that does not have a goal defined.

My Relationship is as follows.

Measures:

Goal Sum = SUM(Goals[Goal])

YTD Actual = TOTALYTD([Actual Sum],Dates[Date].[Date])

YTD Goal = TOTALYTD(Goals[Goal Sum],Dates[Date].[Date])

Actual Sum = SUM('All-Unfiltered Data'[Premium])

[v-yuta-msft]

@Anonymous ,

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

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.

[Anonymous]

Hi,

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.

Use column from Date table on the x-axis.

Did I answer your question? If Yes, Give me a Kudo 

[Greg_Deckler]

Tough to say, 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

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.