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]

Definitely possible although not totally straightforward! One approach is to not relate your ProductB table to your calendar table but use DAX to solve the problem:

I created a quick model to demonstrate:

The tables on the left are those feeding the model and the Pivot on the right is the output. The only relationship is between ProductA (Many) and Time (One). For ease my 'Time' table only has a month granularity but it could happily exist at a day granularity which is more normal.

The ProductA measure is a straight SUM() of the column. ProductB is a more complicated:

=CALCULATE(SUM( ProductB[Sales] ),
        FILTER( 
          ALL( ProductB[Date] ), 
          CONTAINS( VALUES( Time[Year] ), Time[Year], ProductB[Date] ) 
          )
                            ) 
 / 
  IF(HASONEVALUE(Time[Quarter]),4,1)

As you can see, the DAX successfully 'relates' ProductB to the Time table without a relationship. The second part is there to make the subtotals work.

Hope this makes sense.
Jacob

 

[MD610]

Russ,

Try using a measure similar to the following:

=CALCULATE(
                    SUM(Table2[Sales])/4, 
                    ALLEXCEPT(Dates, Dates[Quarters])
               )

 

[Jacob Barnett]

OK, so mine might be slightly over engineered!

Guess it depends whether or not you want to relate that second fact table.

 

[MD610]

Not sure on mine Jacob.

I think my measure needs a tweek to do cross table filtering.

I am assuming the relationship looks like this:

Product A>Dates>Product B

If thats the case then this measure should work:

=CALCULATE(
                  SUM(ProductB[Sales])/4,
                  ALLEXCEPT(Dates, Dates[Quarters]),
                  ProductA
                )

 

[Jacob Barnett]

Mike, that is a d*mn clever trick!

I was thinking you meant a more conventional relationship with ProductB as the many using a calculated field to give each year a date (e.g. 1st Jan) to make the relationship work. Then a simple ALLEXCEPT() construct will do the trick.

 

[MD610]

Jacob,

As I'm sure you know, it is a bit tough to visualize without some sample data but I actually now think mine needs one more "bridge table" in the data model to accommodate the actual data set as I understand it.

There should be a unique Years table between ProductB and Dates.

I think that small modification makes everything work, although I would love to see some sample data to confirm.

 

[Jacob Barnett]

Mike, your second formula definitely works although in my simple data set the ProductB table has unique years so the relationship can be created. I guess this may not be the case in real life.

If we are talking about an extra table, a complex set of relationships and an incredibly advanced DAX concept in the use of Cross Table filtering then I reckon my original solution is looking pretty good .

@ OP, sorry for the digression! If you can pick em out there are 3 different solutions to your issue here, let us know how you get on.

Jacob

 

[Russ Skinner]

Many thanks for the solutions. Will run some tests with my data and let you know how I get on. Also, need to work through your DAX formulas and make sure I fully understand what is going on.

 

[Russ Skinner]

Have both methods working well, but struggling a little to understand the DAX.

Jacob, wonder if you could talk me through your code

=CALCULATE(SUM( ProductB[Sales] ), 
        FILTER( 
         ALL( ProductB[Date] ),  
          CONTAINS( VALUES( Time[Year] ), Time[Year], ProductB[Date] ) 
          )     
                    )
   /    IF(HASONEVALUE(Time[Quarter]),4,1)

Happy with the Calculate...<expression><filter1><filter2><expression><filter1><filter2><expression><filter1><filter2></filter2></filter1></expression>

So expression is Sum(ProductB[Sales]) and then you lose me when you invoke the Filter function and then clear filters with All. Pretty much lost with the Contains line as well.

Thanks,

Russ</filter2></filter1></expression></filter2></filter1></expression>