Forum Discussion
Advocate ITargets set at different grouping levels
If I'm understanding the problem, you have a sales table that has sales at the date level. You have a targets table that doesn't have targets at the date level - targets are associated to a quarter/year only. Right now, to create a visualization with both sales & targets together you're creating a separate table that aggregates both numbers to the same levels (i.e. you're creating a separate table for quarterly numbers and a separate table again for annual numbers). This is working for a fixed visualization, but doesn't allow any drill down from annual to quarterly.
What you really want to do is join both sales & targets to a single date lookup table (with the join at the appropriate level: date for sales and quarter/year for targets) so that you can aggregate both sales & targets by the same year attribute and drill down from annual to quarterly smoothly. (Are you familiar with lookup tables and why they're useful when you want to slice two measures by the same thing?)
If you have a date lookup table, then joining sales to that lookup table is a straightforward relationship. Joining targets isn't as straightforward though. This article, similar to the ones itchyeyeballs shared, describes how to create a measure (BudgetCalc) that connects to a date lookup table on month/year (and goes on to gracefully handle situations when someone drills down too far). You should be able to adapt the principals to your situation (quarter/year).
http://www.sqlbi.com/articles/budget-and-other-data-at-different-granularities-in-powerpivot/
Impactful Individual
Advocate IThanks. While that is helpful to some extend, I am unable to apply a model which uses date as a primary key to link tables.
If Targets are set at global, continent, country and city level - for example - you would be unable to work off a date reference. The problem is as you slice, the target sits in a different level of hierachy.
If the report starts on global performance summary against target, if a filter/slicer is applied for a particiular continent, the model needs to know to then look for a continent level target rather than global or country.
leonardmurphySkilled Sharer
If I'm understanding the problem, you have a sales table that has sales at the date level. You have a targets table that doesn't have targets at the date level - targets are associated to a quarter/year only. Right now, to create a visualization with both sales & targets together you're creating a separate table that aggregates both numbers to the same levels (i.e. you're creating a separate table for quarterly numbers and a separate table again for annual numbers). This is working for a fixed visualization, but doesn't allow any drill down from annual to quarterly.
What you really want to do is join both sales & targets to a single date lookup table (with the join at the appropriate level: date for sales and quarter/year for targets) so that you can aggregate both sales & targets by the same year attribute and drill down from annual to quarterly smoothly. (Are you familiar with lookup tables and why they're useful when you want to slice two measures by the same thing?)
If you have a date lookup table, then joining sales to that lookup table is a straightforward relationship. Joining targets isn't as straightforward though. This article, similar to the ones itchyeyeballs shared, describes how to create a measure (BudgetCalc) that connects to a date lookup table on month/year (and goes on to gracefully handle situations when someone drills down too far). You should be able to adapt the principals to your situation (quarter/year).
http://www.sqlbi.com/articles/budget-and-other-data-at-different-granularities-in-powerpivot/
Did you find a solution to your problem that addressed the need to link data of different granularities beyond just date? I have the same problem before me and am stumped.
