How to convert numeric fields w/implicit measures that come from PowerBI in Pivot table using the Analyze in Excel function

Hi There,

We are trialling the Analyze in Excel or also the PowerBI Publisher for Excel functionality for the content pack/apps we are releasing to our end users/customers.
As shown in the Screenshots when using either of these two features the numeric data in PBI (marked by sigma) just shows up as text in the pivot table . I understand this is because implicit measures are created for these fields in PBI and hence Pivot in Excel doesn’t pull these through. The only way would be to create measures for each field so that it can be used in the Values area in Pivot. However we cannot create measures for each of these fields due to the number of numeric columns in each table. And also because we cannot predict what implicit measure the end user would want for that particular field ( as this is going to be an app that they can then use to connect with other data or also perform their own analysis on)
So is there a way for the end user to create a measure in Pivot in Excel for these non measure fields?For eg can I create a Sum of Actual Total Cost from the field Actual Total Cost in Excel itself after the data has been brought in from PowerBI.

@Nicola

I don’t think there’s a way around that I’m afraid, in the documentation for Analyse in Excel you’ll find:

Excel PivotTables do not support drag-and-drop aggregation of numeric fields. Your dataset in Power BI must have pre-defined measures .

Here is a link to the full documentation:

Thanks Melissa. I feared that was the answer but just thought I’d put it out there for suggestions. We’ll have to figure something out . Thanks anyway

Wait a second, just found this article

So I multiplied a numeric, now text field by 1 (to convert it back to a number) and were able to place it in the values section of my pivot table without any problem. But my fear is that without using actual report Measures it’s hard to predict how these values will behave in context.

So you’ll need to do some serious testing before considering this. Let us know what your findings are.

Hey Melissa,
I tried using the MDX Calculated measures table to create a measure for eg. [Actual Crop Revenue]*1 but it doesn’t give any values. I can drag the now new measure into the Values section but there is no data to show in that field.

. In either case I don’t think this solution would work because it is not something we can offer to out customers a a solution. but thank you for suggesting this.