locked
GetPivotData if a field equals whatever RRS feed

  • Question

  • I'm creating a report in Excel using PowerPivot. I have all my data in the powerpivot window. I create pivot tables and then use this statement to get the total from one of the table.

    =GETPIVOTDATA("[Measures].[Count of R_RECEIVEDDATE]",'Pivot Tables'!$A$31)

    This returns the correct grand total for all the labels in this date. But what if I want to get only the total for one row label.

    Why doesn't "=GETPIVOTDATA("[Measures].[Count of R_RECEIVEDDATE]",'Pivot Tables'!$A$31,"row label", "specific label")

    Thanks!

    Monday, June 3, 2013 5:13 PM

Answers

  • Have you tried using cube functions to calculate the numbers you need?  This seems like a good opportunity.  You actually don't need to make a pivot table to use them (but sinc ethey are just Excel functions you can use them in conjunction with pivot tables or anything else) on the same worksheet if needed). They are essentially writing an MDX statement against your data model.

    http://office.microsoft.com/en-us/excel-help/cube-functions-reference-HA010083026.aspx

    Since I don't know your data model, I can't give you an exact formula.  If your Measure is [Measures].[Count of R_Receiveddate] and the row label you want is [Items].[Item A], then your formula might be something like

    =CubeValue("Power Pivot Data", "[Items].[Item A]","[Measures].[Count of R_Receiveddate]")


    • Edited by Meagan LongoriaMVP Sunday, June 23, 2013 6:21 PM fixed formula
    • Marked as answer by DCDeez Monday, June 24, 2013 12:51 PM
    Sunday, June 23, 2013 6:20 PM

All replies

  • Have you tried using cube functions to calculate the numbers you need?  This seems like a good opportunity.  You actually don't need to make a pivot table to use them (but sinc ethey are just Excel functions you can use them in conjunction with pivot tables or anything else) on the same worksheet if needed). They are essentially writing an MDX statement against your data model.

    http://office.microsoft.com/en-us/excel-help/cube-functions-reference-HA010083026.aspx

    Since I don't know your data model, I can't give you an exact formula.  If your Measure is [Measures].[Count of R_Receiveddate] and the row label you want is [Items].[Item A], then your formula might be something like

    =CubeValue("Power Pivot Data", "[Items].[Item A]","[Measures].[Count of R_Receiveddate]")


    • Edited by Meagan LongoriaMVP Sunday, June 23, 2013 6:21 PM fixed formula
    • Marked as answer by DCDeez Monday, June 24, 2013 12:51 PM
    Sunday, June 23, 2013 6:20 PM
  • I had solved this a little while ago but this is very similar and does what it should.

    Thanks!

    Monday, June 24, 2013 12:52 PM