Forum Discussion

jdunmall's avatar
jdunmall
Icon for Advocate I rankAdvocate I
10 years ago

Analyze in Excel: aggregation of numeric fields

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 i...
MattAllington's avatar
MattAllington
Icon for Community Champion rankCommunity Champion
10 years ago

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. 

itchyeyeballs's avatar
itchyeyeballs
Icon for Impactful Individual rankImpactful Individual
10 years ago

Just to add another couple of postives to Matt's response, by using a measure rather than a 'naked' column its much easier to modify multiple pivots or visualisations in one go i.e. switch from count to distinct count. Also using measures allows you to modify the behavior depending on context, I quite often need to show a sum for individual rows but then an average for the total row.

  • npnigro's avatar
    npnigro
    Icon for Advocate III rankAdvocate III
    10 years ago

    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"?

    • BruceAnderson's avatar
      BruceAnderson
      Icon for Helper II rankHelper II
      10 years ago

      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.  Hoping there is a way to do this using something in Power BI Service and or Excel?

      • plansis's avatar
        plansis
        Regular Visitor
        3 years ago

        indeed, 

        it is NOGO for me as well