# PowerPivot: Trouble with calculated item



## zeus (Dec 2, 2014)

Hello,
I'm using Excel 2010.  I have been scouring Mr. Excel posts and all over the internet for answers on how to create a calculated item in PowerPivot.  All the possible answers I've found want you to create Measures as part of the solution.  I have tried to create a Measure as part of step 1 to the solution.  My "New Measure" looks like this =calculate(sum(Combined[AMOUNT]),Combined[System]="LV").  I click the "Check formula" button and it says "No errors in formula".  Yeah!  So I click OK and I get this error message "The 'Sandbox' cube has no measure groups.  A cube must contain at least one measure group."  I have no idea what that means.  When I compare my formula to the others on the internet, all the internet formulas have something preceding the = sign".  For example SumOfLV:=calculate(sum(Combined[AMOUNT]),Combined[System]="LV").  I tried typing the previous example in for my Measure but I then get an error "The measure formula must start with an equals sign.  My PowerPivot pivot table has ComputerSystem as a column label (one system is called 'ABC' and the other is called 'LV').  My goal is to have a "calculated item" calculate the difference between the two systems ('ABC'-'LV'). Can anyone help me understand what I need to do to achieve this in PowerPivot?

I thank you for your time.


----------



## scottsen (Dec 2, 2014)

*Re: PowerPoint: Trouble with calculated item*

That sounds a lot like... a corrupt file  

I would make sure you don't have any "stray" excel processes running in the background (use task manager to find/kill them)... but if it persists, you will likely need to start over.

That error is certainly not something *you* did wrong.


----------



## PentaGalCXO (Dec 3, 2014)

*Re: PowerPoint: Trouble with calculated item*

Sounds like you are trying to add a measure from the Excel interface. I never do that (its quirky, non-intuitive, and i usually regret it later). I always add my measures in the PowerPivot Manager interface. When you see examples such as "SumOfLV:=" those are measures defined in the PowerPivot Manager.

wish i could post a quick screen shot here... your formula with the colon works fine for me just as you have it...


----------



## zeus (Dec 3, 2014)

*Re: PowerPoint: Trouble with calculated item*

Thank you for the responses!  I shut down Excel and tried to create my Measures again.  It worked this time.  Thank you scottsen!

PentaGalCXO, I have a question for you - I don't think I understand the two places where I can add a Measure.  I'm using the Pivot Table ribbon tab then clicking on "New Measure".  This must be the Excel interface you mention.  How to I access the PowerPivot Manager interface?

Regardless, this seems like a really odd, backdoor way to create a calculated item in order to simply calculate the difference between two columns.


----------



## PentaGalCXO (Dec 3, 2014)

*Re: PowerPoint: Trouble with calculated item*

From the Excel Ribbon select PowerPivot then Manage. That will bring up the window that allows you to see the data model including the data tables you have imported. From here you can create calculated columns, measures, relationships... This is where the real work is done


----------



## zeus (Dec 4, 2014)

*Re: PowerPoint: Trouble with calculated item*

I was digging into things last night, trying to figure out why I don't have the "Manage" option.  I'm wondering if we're not using different versions of Excel/PowerPivot.  I'm using 2010 and when I click on the PowerPivot tab within the ribbon, my leftmost button is called "PowerPivot Window".  When you click on this button, you're taken to a separate screen where you import your data tables, but there is no option to add a new measure in the new screen.  The "New Measure" button is on the PowerPivot tab to the right of the "PowerPivot Window" button.  I wanted to attach some screen shots but I either don't have access to or I simply can't figure out how to.

I was able to get a column that shows the difference using my "New Measure" button.  Like I said, it's definitely not a glamorous process but at least it accomplished what I needed it to (after creating a "set" that excluded certain columns).

Thank you for your comments on my post, I really appreciate it!


----------



## PentaGalCXO (Dec 4, 2014)

*Re: PowerPoint: Trouble with calculated item*

You are sooo close... In the lower part of the PowerPivot window is an Excel like freeform grid. You can insert DAX formulas anywhere in that grid. The DAX formula defines the desired measure and must be of the form <name>:=<DAX Formula> such as Total:=sum([Revenue]) which will sum up the Revenue column. Then when you create your PivotTable, <name> will appear as a measure under the name of the Table you created the measure on. Just add that measure to the Values section of your Pivot Table. PowerPivot is now your oyster.


----------



## zeus (Dec 5, 2014)

*Re: PowerPoint: Trouble with calculated item*

OOOOOOHHHHHHHHHH, I get it now   You enter the formulas in the same place where you import your dataset.  You just pick an empty column, enter your formulas and away you go!  I did some playing with my file and simplified the dataset just to see if this was gonna work, it did!  I still have to start the formula with a "=" not "xxx:=".  I tried the latter option and PowerPivot told me that I must start formulas with =.  Thank you so much for sticking with me and helping me out with all this!  Case closed


----------



## scottsen (Dec 5, 2014)

*Re: PowerPoint: Trouble with calculated item*

Looks like you found how to add a new calculated COLUMN.  To add a new calculated FIELD you enter it in the grid below the table/columns. 

Calculated Fields vs Calculated Columns | Tiny Lizard


----------



## zeus (Dec 8, 2014)

*Re: PowerPoint: Trouble with calculated item*

ok scottsen, I think I'm making progress, but adding some confusion.  I played with things on my home computer on the weekend and I now understand where you're talking about when you say to enter the calculated FIELD below the table/columns.  There's a separate little section below the grid where your data is appears when it's imported.  Ok great, go back and try to do this on computer #2 thinking that I must really be blind if I couldn't see this before...but computer #2 doesn't have that second section below the grid where i can enter calculated field information.  (I read the link you attached in your last post "Calculated Fields vs Calculated Columns", thank you, that was helpful).  As a work around, I created a pivot table, which activated my New Measure button, and created 3 separate "New Measures": 1) sum of computer system #1 2) sum of computer system #2 3) difference between computer system #1 & 2.  Then I created a set so I could eliminate one column that showed the difference (because it appeared twice).  So now I have a pivot table that shows both systems as columns and the difference between the systems.  How'd I do, is this the way you'd do it?  Any idea why I can't see that little grid section below the area where my data was imported?  Thanks again for your help!


----------



## zeus (Dec 2, 2014)

Hello,
I'm using Excel 2010.  I have been scouring Mr. Excel posts and all over the internet for answers on how to create a calculated item in PowerPivot.  All the possible answers I've found want you to create Measures as part of the solution.  I have tried to create a Measure as part of step 1 to the solution.  My "New Measure" looks like this =calculate(sum(Combined[AMOUNT]),Combined[System]="LV").  I click the "Check formula" button and it says "No errors in formula".  Yeah!  So I click OK and I get this error message "The 'Sandbox' cube has no measure groups.  A cube must contain at least one measure group."  I have no idea what that means.  When I compare my formula to the others on the internet, all the internet formulas have something preceding the = sign".  For example SumOfLV:=calculate(sum(Combined[AMOUNT]),Combined[System]="LV").  I tried typing the previous example in for my Measure but I then get an error "The measure formula must start with an equals sign.  My PowerPivot pivot table has ComputerSystem as a column label (one system is called 'ABC' and the other is called 'LV').  My goal is to have a "calculated item" calculate the difference between the two systems ('ABC'-'LV'). Can anyone help me understand what I need to do to achieve this in PowerPivot?

I thank you for your time.


----------



## scottsen (Dec 8, 2014)

*Re: PowerPoint: Trouble with calculated item*

That "New Measure" button is identical to using the calculatoin area in the power pivot window.

Speaking of which, launch the PowerPivot window... and on the Home tab at the far right you will see "Calculation Area".  It's just a toggle, and I suspect yours is off.


----------



## zeus (Dec 8, 2014)

*Re: PowerPoint: Trouble with calculated item*

well, it's been an interesting afternoon.  I've been Googling, trying to figure out why I don't see 'Calculation Area' in the location you mention and I've come across some postings from people with the same problem...no solutions posted.  At least I can create my calculated fields using the "New Measure" button so I'll chalk it up to an odd, unresolved problem for now.  If everything else works ok then that's the important thing.  Thank you for your devoted assistance to my problem.  I really do appreciate it and now I can use PowerPivot a little smarter than I could before


----------



## CynnieStar (Aug 28, 2015)

*Re: PowerPoint: Trouble with calculated item*

I need your help. I am trying to move a data model currently created in separate pivot tables into one data model in powerpivot. From there I am creating pivot tables. However, in the old model, there was a calculated item which is a delta (difference) between values for 2015 and 2014 and 2014 and 2013 for different values: hours, net fees etc. and in old model the formula listed as calcuated item looked like this:

Calculated Item		
Solve Order	Item	Formula
1	'Delta FY14-FY13'	='2014'-'2013'
2	'Delta FY15-FY13'	='2015'-'2013'
3	'Delta FY15-FY14'	='2015'-'2014'


I tried to create the same measure in powerpivot but i get stuck as i think i can only do it for 1 value at a time:

DELTA 2015-2014:=CALCULATE(SUM(GESdata1[PerfHours]);GESdata1[FY]=2015)-CALCULATE(SUM(GESdata1[PerfHours]);GESdata1[FY]=2014)

but i would need the same for [Fees], [GrossFees], and many many other.

What was done in old model via calculated item was simple and worked for all measures:

You could easily make a pivot with values, which were calculating deltas with use of the above mentioned calculated item:

COMPARISON	

FY	             HOURS	GROSS FEES	NET FEES
Delta FY14-FY13	 2.374 	 -458.438 	 538.496 
Delta FY15-FY13	 5.938 	 509.483 	                1.138.859 
Delta FY15-FY14	 3.564 	 967.920 	                00.363 


Can you help me to create it in powerpivot too?

THanks


----------

