Excel 2010 PowerPivot - Using PivotTable Result as Input for Second PowerPivot
Suppose I had the following table in SQL Server DB, I wanted to do Excel PowerPivot ( Excel 2010 version ):
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 how it works, I need to, for lack of better wording, its double pivot.
What do I need to do:
- Return
Sum(Value)
BDate
andGrp
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 have to, say, perform another calculation (besides sum()
) on this data - for the product of values for each day:
Output2:
Grp: Value:
a 36
b 42
c 36
So, I know how to use PowerPivot to get the first result set, and I'm guessing that I could create a calculated field with many duplicate values for each group value to calculate right away Output2
, but I'm wondering if there is a way, perhaps, to print the first output instead then use this summary pivot table as a sign in to PowerPivot.
Overall, I'm new to PowerPivot, so any advice on how to do this would really be appreciated!
Thank!!
source to share
John
there is a neat function called Summarize that allows you to create a table on the fly that can be passed as a parameter to FILTER or any other DAX functions that require a TABLE.
I copied your sample data (with the base table name) and if you run 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 the Grp values was tricky since there is no PRODUCTX function. But to get your daily average, you can use this:
AVERAGEX
(
SUMMARIZE (
basetable,
'basetable'[Date],
basetable[Grp]
)
, CALCULATE(AVERAGE(basetable[Value]))
)
The result might then look like this (keeping the same structure as Output1, but using Output1 to do the average calculation).
Not sure if I fully answered your question, but I'm sure SUMMARIZE is something to consider when you say "a way [...] to output the first output and then use this summary pivot table as input to PowerPivot ".
However, be aware of performance issues . SUMMARIZE can be quite "demanding" :)
Hope this helps!
source to share