Fields pulling through to Excel incorrectly

[PowerBIWhat]

Hi,

I'm experimenting with the Get Data from Power BI datasets functionality in excel. I can connect fine to my power bi dataset and I am then given the ability to create a pivot table. So far so good!

The issue I'm having is that some fields are not available to be selected as values, only as rows or columns. In some cases, the column is formatted as a number in the dataset but when I'm trying to use it in the pivot table I can only use it as a row or column. Not as a value so I can't take the average or sum of the column as an example. In fact, I can't even display it.

An example is a column Sales Value formatted in Power Bi as £,000 but i can only use it as row or a column. This makes me think it is pulling through to excel as text. I can't seem to find a way to change the column in Excel.

It also means I don't have the ability to pull the Sales id column into the values of the pivot table and perform a count to find the number of sales.

I've only just started playing with Excel so maybe there is a secret to this.

If anybody can help..

Thanks in Advance,

O

[PowerBIWhat]

On a second look, it appears that the only items I can use as values are measures in the dataset.

[AlexisOlson · Accepted solution]

Yes, only explicit measures will pull through to Excel.

See here for more detail on implicit vs explicit measures:
https://radacad.com/explicit-vs-implicit-dax-measures-in-power-bi

[PowerBIWhat]

Thanks, that is v helpful. Is there a way in excel to create explicit measures? Or do I have to create the explicit measures in PBI before connecting to the dataset in Excel. It feels a bit messy creating a measure for something which doesn't need a measure in PBI.

[AlexisOlson]

It doesn't seem like it.

• Excel PivotTables don't support drag-and-drop aggregation of numeric fields. Your dataset in Power BI must have pre-defined measures. Read about creating measures.

From these documentation sources:

https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-connect-power-bi-datasets-excel

https://support.microsoft.com/en-us/office/create-a-pivottable-from-power-bi-datasets-31444a04-9c38-...