Measures as attributes when consuming data from a Power Pivot

Question

I created a Power Pivot workbook that has information from our store (the plan is to connect to this document and consume the data), basically is something like this.

CostumerID | QtyPurchasedProductA | QtyPurchasedProductB | QtyPurchasedProductC ...

Everything is working fine when I use the Power Pivot data as a Pivot Table, but I when I upload the workbook to a SharePoint site and connect to the PowerPivot from another workbook, all of the measures (QtyPurchasedProductA , QtyPurchasedProductB, QtyPurchasedProductC) are shown as attributes instead of measures.

This happens with pulled from my database as well as custom DAX fields.

Any idea why this is happening? Is there a way I could specify on my Power Pivot Central Document? (the one I uploaded to sharepooint)

Thanks

Answer 1

When working with a pivot table (or pivot chart) based on a PowerPivot container local the workbook, Excel will "automagically" apply an aggregation function (Count, Sum, etc) to any field placed in the values/details section. My guess is that this "feature" is intended to make PowerPivot more user-friendly for the average business user...however I think promotes bad habits.

So, you'll want to go back in the local copy of your PowerPivot workbook and explicitly defined the measures. The easiest way to do this is to select the column and choose from one of the listed aggregation functions (see below):

For complex measures, you can use a DAX expression in the calculation area to define a calculated measure.

Once that's done, upload the PowerPivot workback back to SharePoint and you should see the measures when you connect to it and try to use it as a data source in other workbooks.