PowerPivot: Trouble with calculated item

[zeus]

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]

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]

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]

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