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 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]

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]

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]

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]

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]

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]

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]

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]

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!