Analyze in Excel: aggregation of numeric fields

Question

I'm having trouble understanding how to use the new Analyze in Excel feature. The documentation says:

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

If I have a numberic column called Total Sales in the dataset, I cannot summarize it in a pivot table. Odd.

Does that mean I need to create a second column called Total Sales 2 that is a Measure of SUM([Total Sales]). There are now two fields in the dataset that contain the same data, but have different names. That is confusing. Do I then go through and hide the column?

Why can't PowerTables aggregate numeric fields? How should I approach numeric fields in the dataset that aggregate nicely inside PowerBI without measures but do not aggregate in Excel properly?

-Jeff

mattallington · Answer

You are correct, but it is not as bad as you are imagining. When you drag a numeric column into values in Excel, you are creating an implicit measure "on the fly". This is not supported with "Analyze in Excel" nor other deployments of SSAS being consumed in a pivot table in Excel. You don't need to create another column, you need to write explicit measures for each column you want to Analyze. Just write the measure in Power BI and then hide the source column. The upsides are you learn to write some DAX, you get to specifically specify the name (compare "Sum of Extended Amount" with "Total Sales") plus you get to set the formatting in the measure, and at will flow through to the pivot.&nbsp;

npnigro · Answer

The problem with this method is that it strips out a large part of the user interface -- implicit measures are baked into Excel and Power BI. Requiring these explicit measures to do anything with the data is a noticeable inconvenience and makes for very confusing and limited reports. If you hide the data columns in Power BI, then you no longer have access to the features related to implicit measures. If you keep the columns, you confuse Excel users. Is it possible for support of implicit measures to be added to "Analyze in Excel"?

bhanuregonda · Answer

Hi,&nbsp;I have a powerbi report in which I have YTD calculated measure and i am getting the correct result in power BI for the %YTD value out of it, but when I am analyzing that report in Excel - i am not getting the same values as in Power bi in that column. Not sure why is it different&nbsp;from power bi to analyze excel.&nbsp;Please help me with this.&nbsp;Thanks

bruceanderson · Answer

I just discovered this limitation and my excitement for Analyze in Excel went woosh!I think we would have to make the measure in Power BI Desktop, but i have uploaded most of my reports from Excel data sources and to have to re-do them in Desktop would be a nightmare.&nbsp; Hoping there is a way to do this using something in Power BI Service and or Excel?

mattallington · Answer

It is not a bug. It is working as designed. It has always been this way with SSAS (originally Multi Dimensonal and now also Tabular). The easiest way to make it work for you is to use Tabular Editor Scripting. I cover how to do that in this blog

https://exceleratorbi.com.au/introduction-to-tabular-editor-for-business-users/

Note, this is a bit old now (i need to update it). you don't need to create a template file anymore. The new approach is covered here (Tabular Editor 3, but also works for TE2, the free version)

https://exceleratorbi.com.au/external-tools-in-power-bi-desktop/

Forum Discussion

Analyze in Excel: aggregation of numeric fields

10 Replies

Related content

numeric slicer for aggregated Y axis

Aggregate in numerical order

Best Practice Analyzer

Group by on eventstream's lakehouse target doesn't aggregate numeric fields

Data Aggregation issue