Questions about PowerPivot

[rhinofromTurkeyParticipant Started: 0Replies: 7Total: 7]

Question 1

What is the difference with PowerPivot in Excel and PowerPivot in the PowerPivot designer (when you click on “PowerPivot Window” in excel 2010) with regards to formulas

I imported a SQL table into the PowerPivot Designer and create a powerpivot table based on the data which was imported.

I’m  trying to add a column in the designer and it works fine as long as the formula is correct, When I try to add a column (via measures) it tells me that  “The value for column ‘Department” in table ‘report’ cannot be determined in the current context”

I’m confused since the Column and Table are present in the designer?

Question 2

I’m still learning PowerPivot (read:green) so some of this stuff is foreign

When Creating a powerpivot table within Excel, why cant I get the information in designer to be identical to the way it appears in the designer?

If I was able to add a measure maybe this wouldn’t be such an issue – but measures, from what I can tell, add a column based on input from the original query..

Or am I missing something?

Thanks I hope this all makes sense

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hello,

I am pleased you are asking questions, that is how I also learned my answers.

Regarding Question #1:

All measures must be aggregations.

If you are “creating a measure” in the values drop zone of the pivot field list, Excel is providing an aggregation for you, which by default is a sum for a numeric column and count for other data types. This type of measure in Excel is sometimes referred to as an “implicit measure” because Excel creates it for you (when you look at the column name in the Values drop zone, you will see Excel has changed the name to something like Sum Of… or Count of …

If you are creating a measure in the Power Pivot window you are responsible for specifying the aggregation. If you do not specify an aggregation for a measure inside of Power Pivot, you will get the message “column … in table … cannot be determined in the current context”. Because you must specify the aggregation inside of Power Pivot, this type of measure is often called an Explicit measure.

Regarding Question #2:

If I understand this question, you are asking why in a Power Pivot pivot table (e.g., on the Excel side), the pivot table takes on an outline form, where in the Power Pivot window a table does not appear in an outine form.

The answer to this question, is that in Excel you can make your “Power Pivot” pivot table appear in a tabular or in an outline format. For pivot table rows you can change between outline and tabular form by right clicking on the little arrow just to the right of a field name in the Rows drop zone. When the context menu appears, choose Field Settings. In the dialog box that then appears, choose the Layout and Print tab. There you will see options for showing items in outline form or tabular form; choose as appropriate. Also important is a checkbox for “Repeat Item Totals”.

[rhinofromTurkeyParticipant Started: 0Replies: 7Total: 7]

TomAllen:

Thank you very much for the reply –

The answer to question 2 is kind of what I was looking for, I’ll have to better prepare the question and then I’ll get back to you. However, it did shed some light on a different topic which will be helpful in the future – so thank you for that but of information you added.

rhinofromturkey

[Author]

Posts