CODEWITHSUNDEEP
powerpivot
Guenh
Guenh

Reputation: 21

Powerpivot adding calculated item to a field

I have data in a table stored in an Excel file. I linked this table into a PowerPivot Data Model, and from that Data Model I want to create a Pivot in the same Excel file. In this table one column contains split of data into: Budget, Last Year, Prior Forecast, Current Forecast. I want to add this field as Pivot coulmns, but I would like to add additional calc item (like in normal Excel Pivot Table I can add Calculated Item) with calculation: [Current Forecast] - [Prior Forecast]. I checked various pages, forums, etc. and I have not found any guidance on how to add such a calc item to a field in PowerPivot. My input looks like this:

Sample data:
Category           Client   Amount
Current Forecast    XYZ        600
Current Forecast    ABC       1000
Current Forecast    DEF        100
Prior Forecast      XYZ        500
Prior Forecast      ABC       1200
Budget              XYZ        800
Budget              ABC        900
Budget              DEF        100
Last Year           XYZ        700
Last Year           ABC        500

From this data I want to create a Pivot that would look like this:

        Current    Prior               Last
Client  Forecast   Forecast   Budget   Year    FoF    YoY
XYZ          600        500      800    700    100   -100
ABC         1000       1200      900    500   -200    500
DEF          100                 100           100    100

In PowerPivot, I want to add two additional columns, perhaps as calculated items in the Category field:

FoF=[Current Forecast]-[Prior Forecast]
YoY=[Current Forecast]-[Last Year]

On below screenshots it is better visible, what I want to achieve:

screenshot1

screenshot2

I can't add this to my source data as the number of rows between current forecast and prior forecast are not in the same amount and not in the same order (they are extracted from another system)

Upvotes: 2

Views: 22375

Answers (2)

jeremyparty
jeremyparty

Reputation: 79

How to do realize calculated item in PowerPivot, usually it has many columns as KPI need to compare, amount, quantity, COST ect. as below 2. secondly most times the data that you want to put in comparison has to lie in row. Really ache to seek a solution, but seems Dax or PowerPivot has lost the function of calculated item.enter image description here

Upvotes: -1

Petr Havlik
Petr Havlik

Reputation: 3317

Guenh, this should be quite straightforward:

Depending on your PowerPivot versions, you either create a new measure from PowerPivot ribbon (Excel 2010 Add-in: New Measure button). In Excel 2013, it was renamed to Calculated Field, still available via PowerPivot ribbon:

enter image description here

Anyway, I downloaded your data and created new measures (= calculated fields) for all of your columns, which was the key. The way your data is structured is not ideal, but it's certainly doable. First you have to sum the amount for each category, so use this simple CALCULATE formula:

-CALCULATE(SUM([Amount]),Table1[Category]="Budget")

What it does is that it sums the amount values, but only for rows have Budget in the Category column. The example above is defined for the Budget category; include the other three categories as well (simply duplicate the formula and change the second parameter in CALCULATE: instead of "Budget", use "Last Year", etc.

Then create your FoF and YoY calculations by simply using already created measures:

enter image description here

If you then add new PivotTable (from PowerPivot window), simply drag Client Name to rows section and all the measures to Values section.

With that, you should get your desired result table as shows below. Here you can download the source file (for Excel 2013).

Hope this helps :)

enter image description here

Upvotes: 6

Related Questions