locked
How implementing a calculated item in PowerPivot RRS feed

  • Question

  • Hi,

    I need to know if it is possible to implement a calculated item as for a classic pivot table. A calculated item is not a measure and not a calculated column, concepts of PowerPivot.
    With a calculated item I could determine the difference between Actual and Budget put it on rows as a category dimension.

    Any suggests to me, please?

    Many thanks

    Tuesday, May 7, 2013 5:48 PM

Answers

  • DAX measures can fully replace the role of calculated items in classic pivots.

    Assume your table looks like this

    Category Year Attribute Value
    Magazin Adds 2013 Budget 80000
    Magazin   Adds 2012 Budget 60000
    TV Adds 2012 Actual 200000
    Internet Adds 2013 Budget 250000
    TV Adds 2013 Budget 180000
    Internet   Adds 2013 Actual 300000
    TV Adds 2012 Budget 210000
    Magazin   Adds 2012 Actual 80000
    Magazin Adds 2013 Actual 60000
    Internet   Adds 2012 Budget 300000
    Internet Adds 2012 Actual 300000
    TV Adds 2013 Actual 210000

    And you add it to the Power Pivot Table.

    You can create measures for actual and for budget with this DAX formulas:

    Unpivot_Actual:=calculate(sum([Value]),Advertising_Unpivot[Attribute]="Actual")

    Unpivot_Budget:=calculate(sum([Value]),Advertising_Unpivot[Attribute]="Budget")

    And a third measure for the remaining budget :

    Unpivot_Free:=[Unpivot_Budget]-[Unpivot_Actual]

    Now you can create any pivot and get the totals you want :

    Unpivot_Free Column   Labels
    Row Labels 2012 2013 Grand Total
    Internet Adds 0 -50000 -50000
    Magazin Adds -20000 20000 0
    TV Adds 10000 -30000 -20000
    Grand   Total -10000 -60000 -70000

    Sunday, August 25, 2013 8:50 AM

All replies

  • I think the best you will be able to do is to use the 'Show Values As' function to show the budget as a % of the sales. In 2010 Calculated fields cannot be created (in 2013 the concepts of calculated field and measure are combined).

    This isn't necessarily bad news as it simply means approaching the problem using DAX which in the long run will far better.

    Suggest you start with this simple approach by Rob Collie and then move onto a really clever alternative outlined in this article by Marco Russo.


    Jacob | Please mark helpful posts and answers

    Tuesday, May 7, 2013 11:45 PM
  • Hi Jacob, thanks for your reply.

    I explain better the issue. I need to represent Actual and Budget and the Actual vs Budget on rows in a pivot table. So, I've created a Category dimension having Actual and Budget as members. I want to achieve this representation:

    putting the Category dimension between the row labels of the pivot table.

    With a classic pivot table I could simply to add a new calculated item for Actual vs Budget with the formula Actual - Budget.

    How can simulate this mechanism in PowerPivot, if it is possible? It isn't really right to say that Actual and Budget are two distinct measures; the category features the related amount.

    More probably I will use DAX expressions ...

    Thanks

    Wednesday, May 8, 2013 7:34 AM
  • I would dearly love to see an answer for this. Have you found a solution?

    Like you, I liberally used calculated items in Pivot Tables in the past. I love PowerPivot but am missing the Calcualted Item functionality.

    If you have any news on this then I would be grateful if you could share here.

    Many thanks.

    Tuesday, July 30, 2013 3:41 PM
  • Hi Bruce, any direct solution.

    I'm using DAX formulas.

    Bye

    Wednesday, July 31, 2013 12:36 PM
  • Jacob posted two solutions.

    Did you go through those solutions?

    Thanks!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Thursday, August 22, 2013 6:27 PM
  • Hi Ed, for me it should be more interesting to simulate the Excel calculated item without using DAX formula. For my task, I've need to implement a category dimension to have a generic measure or calculated field, with Actual, Budget and Actual vs Budget as values. Remember, that I haven't used two different measures.

    So, I've created a DAX formula without using the "Show Values As" function, perhaps not much useful for my purposes.

    Thanks

     
    Friday, August 23, 2013 5:50 PM
  • DAX measures can fully replace the role of calculated items in classic pivots.

    Assume your table looks like this

    Category Year Attribute Value
    Magazin Adds 2013 Budget 80000
    Magazin   Adds 2012 Budget 60000
    TV Adds 2012 Actual 200000
    Internet Adds 2013 Budget 250000
    TV Adds 2013 Budget 180000
    Internet   Adds 2013 Actual 300000
    TV Adds 2012 Budget 210000
    Magazin   Adds 2012 Actual 80000
    Magazin Adds 2013 Actual 60000
    Internet   Adds 2012 Budget 300000
    Internet Adds 2012 Actual 300000
    TV Adds 2013 Actual 210000

    And you add it to the Power Pivot Table.

    You can create measures for actual and for budget with this DAX formulas:

    Unpivot_Actual:=calculate(sum([Value]),Advertising_Unpivot[Attribute]="Actual")

    Unpivot_Budget:=calculate(sum([Value]),Advertising_Unpivot[Attribute]="Budget")

    And a third measure for the remaining budget :

    Unpivot_Free:=[Unpivot_Budget]-[Unpivot_Actual]

    Now you can create any pivot and get the totals you want :

    Unpivot_Free Column   Labels
    Row Labels 2012 2013 Grand Total
    Internet Adds 0 -50000 -50000
    Magazin Adds -20000 20000 0
    TV Adds 10000 -30000 -20000
    Grand   Total -10000 -60000 -70000

    Sunday, August 25, 2013 8:50 AM
  • In order to implement a calculated item, allowed for a classic Excel pivot table, using DAX formulas represents a workaround and not the more fine solution.

    The calculated item exists for a classic pivot table and so I ask myself because not having the same feature for a PowerPivot table. In this way, it should be available a rapid and natural solution without using any Dax formulas.

    Bye

    Wednesday, September 25, 2013 7:31 AM
  • HI

    I also looking for solution, what you suggested is calculate field, the result is in another column, what I need is calculated item, which mean that the calculation will be in a row and not columns

    Sum of Sum of SalesAmount Column Labels
    Row Labels 2002 2003
     Accessories             14,468
     Bikes                   500,365          865,693
     Clothing               6,508
     Bikes -Accessories                   500,365          851,225
     Grand Total                1,000,730       1,737,893

    see the "bikes-Accessories" I can easily create in the simple pivot table when I stand on the row label: filed---calculated item and then I simple create the formula. but in pivot table created from powerpivot the it is not optional, what is the solution for that?

    Thanks

    Dekel from Israel

    Thursday, November 28, 2013 12:40 PM
  • Sum of Sum of SalesAmount Column Labels
    Row Labels 2003
     Accessories                     14,468
     Bikes                   865,693
     Clothing                       6,508
     Bikes -Accessories                   851,225
     Grand Total                1,737,893

    this picture is more accurate

    Thursday, November 28, 2013 12:41 PM
  • I want to say that exist the OLAP PivotTable Extensions that allow to add a measure or a calculated item.

    See http://olappivottableextend.codeplex.com/.

    Bye

    Friday, December 6, 2013 10:17 AM