A granularity problem?

[Russ Skinner]

Not sure if the following is possible with powerpivot, but the following is a simplified example of what I'm trying to achieve.

I have (2) product tables (linked to powerpivot), both related to a calendar table by a date field.

Table 1 (product A) lists items sales on a monthly basis.

Table 2 (product B) lists item sales on an annual basis.

I would like to produce a pivot table that reports sales of products A and B per calendar quarter in rows by years in columns. Whilst this is straight forward for product A using quarters defined in the calendar table I cannot figure out a way to break product B down into quarters (annual sales/4) and then assign this value to each quarter.

Any ideas greatly appreciated.

I'm reluctant to have to go back to the Product B source table and break the annual figure down manually.

Thanks,

Russ Skinner

 

[Jacob Barnett]

Russ,

The CALCULATE() function accepts a table as the second argument, the expression - initially it was not totally intuitive to me that this should work but it does. Obviously the table returned has to be either related to the measure being calculated or the table itself. It's more usual for the table being filtered to being a related dimension table i.e. a Date table.

The FILTER() iterates through each row and returns a table based on the conditions stipulated. In this case the ALL() isn't actually required because there is no relationship but in general you need it in a 'pattern' of this type. The CONTAINS() simply tests whether or not the value in the table column matches the value in the stipulated filter context. Only those rows that are TRUE are returned in the table.

Going back to the original example, the FILTER() matches the filter context in the Pivot table with the value in the date column of ProductB

A good piece about the use of FILTER() in CALCULATE() is here: The Greatest Formula in the World, Part 3 « PowerPivotPro

The article I got the CONTAINS() concept from is: Budget and Other Data at Different Granularities in PowerPivot | SQLBI

 

[Russ Skinner]

Big thanks Jacob, I'm with it now. Very clever stuff!

Will check out the articles you mention.

Thanks again for your help with this.