Forum Discussion
Advocate IAnalyze in Excel: aggregation of numeric fields
Community ChampionYou 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.
formatting and aggregation is already setup in the powerbi model
it must be a bug i cannot aggregate numeric values straight in Excel,
microsoft, please support this as soon as possible
right now, tabular from powerbi is unusable in Excel.
- MattAllington
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/
i went through the process of converting fields into measures and it feels like a lot of tedious repetition work. why measures are not created automatically for all numeric fields?
the fields have aggregation setup, all you need is to ecapsulate the field name into a DAX aggregation function and keep it consistent with the fields aggregation choice. It is much easier to manage it via the model where aggreation is supported via GUI. Dax requires code editing any time , and would scare 90% of the users away.
The model fields can be used in Powerbi as numeric, but when you go to Excel , they will not be considerd as numeric. so this is inconsistent behaviour. not a bug per ce but is this user fiendly - NO. can it be automated , - yes, as I suggested.