# How implementing a calculated item in PowerPivot

• ### 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.

Many thanks

Tuesday, May 7, 2013 5:48 PM

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

Assume your table looks like this

And you add it to the Power Pivot Table.

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

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.

Tuesday, May 7, 2013 11:45 PM

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)

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

And you add it to the Power Pivot Table.

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

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.

Bye

Friday, December 6, 2013 10:17 AM