Excel 2010 PowerPivot - Using a PivotTable result as the input for a second PowerPivot

Question

Suppose I had the following table in a SQL Server DB I wanted to do an Excel PowerPivot (Excel 2010 version) on:

Date:   Grp:   Value:
1-Jan   a      1
1-Jan   a      2
1-Jan   b      3
1-Jan   c      4
2-Jan   a      5
2-Jan   b      6
2-Jan   a      7
2-Jan   b      8
2-Jan   c      9

And the way it works is that I need to, for lack of better wording, double-pivot it.

What I need to do is:

Return Sum(Value) By Date and Grp

Output:

Date:   Grp:   Value:
1-Jan   a      3
1-Jan   b      3
1-Jan   c      4
2-Jan   a      12
2-Jan   b      14
2-Jan   c      9

THEN, I need to, say, perform another computation (other than sum()) on this data - For the product of values for each day to get:

Output2:

Grp:    Value:
a       36
b       42
c       36

So, I know how to use PowerPivot to get the first set of results and I'm guessing I could create a calculated field with many repeated values for each group value to right away calculate Output2, but I'm wondering if there's a way to maybe output the first output and then use that resulting pivot table as an input into PowerPivot.

Overall, I'm a PowerPivot newbie, so any advice about how to do this would be really appreciated!!

Thanks!!

Petr Havlik Petr Havlik · Accepted Answer · 2014-10-07T23:41:29

John,

there is a neat function called Summarize which allows you to create a table "on-the-fly" that can be passed in parameter for FILTER or any other DAX functions that require TABLE.

I have copied your sample data (with the table name being basetable) and if you run the the code below, you should get the same result as in Output1 (I suggest you use Dax Studio for this).

SUMMARIZE (
            basetable,
            'basetable'[Date],
            'basetable'[Grp],
            "Value", SUM('basetable'[Value])
        )

Now for Output2 - calculating the product for Grp Values was tricky as there is no PRODUCTX function. But to get an daily average, you could use this:

AVERAGEX
  (
    SUMMARIZE (
      basetable,
      'basetable'[Date],
      basetable[Grp]          
    )
  , CALCULATE(AVERAGE(basetable[Value]))
  )

The result then could look like this (keeping the same structure as Output1, but using Output1 to perform the average calculation).

enter image description here

Not sure if I entirely answered your question, but I am convinced SUMMARIZE is something to consider when you talk about "a way to [...] output the first output and then use that resulting pivot table as an input into PowerPivot."

However, be aware of performace-related issues. SUMMARIZE can be quite "demanding" :)

Hope this helps!

Excel 2010 PowerPivot - Using a PivotTable result as the input for a second PowerPivot

1 Answers