Targets set at different grouping levels

[r4sh1d]

In an example, there is a detailed level table where sales are recorded at transaction level with a column for month, quarter and year.

In terms of aggregating the results for visualisation, I am able to easily sum the sales at various levels e.g. annual, quarterly, monthly etc.

The targets are set at annual and quarterly level. I am not sure how to bring these into visualisations.

I have crated grouped tables in the Query Editor, aggregating the sales at the same level at the targets and then loaded the targets in a seperate table with a relationship between the appropriate level of aggregation e.g. annual summary table related to annual target table.

This means the reports I create for performance to target serve their purpose but do not offer flexibility in terms of being able to drill down. Ideally, I would be able to use a single data set, with aggregation done within the creation of visualisations - but how can I bring the targets into it then?

[itchyeyeballs]

Try these

http://www.powerpivotpro.com/2012/01/salesbudget-integrating-data-of-different-grains/

http://www.daxpatterns.com/handling-different-granularities/

[r4sh1d]

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

[jwil]

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.

[leonardmurphy · Accepted solution]

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/