Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

managed aggregation with direct query table - using measures and average values - not possible?

Hi,  I am not seeng the option for using average in managed aggregations.  e.g. see screenshot below Also can we use measures in managed aggregations?  the answers seems no.  , you can not se...
  • Anonymous's avatar
    Anonymous
    6 years ago

    Got the answers I need!!!
    There is an amazing article (2019/11) by Shabnam Watson that provided me with the answers:
    https://shabnamwatson.wordpress.com/2019/11/21/aggregations-in-power-bi/

    To get an average value, create a measure against the details table where the masure can be broken down to the basic calculation of sum/count  - this measure will hit the manged aggregation table that is in memory when placed into a visual.  Note, creating a measure using the divide() with sum and count calculation against the details table works but a measure created with the average() will not work
    e.g.  

    avg pricepaid = DIVIDE(sum(ProperyDetails[PricePaid]) , COUNTROWS(ProperyDetails) )  - works and hits the aggrgated table
    avg pricepaid 2 = AVERAGE(ProperyDetails[PricePaid])   - does not hit the aggegated table.


Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi amitchandak 
Managed aggregation provides 4 columns for configuring the aggregation:  

Aggregaton Column, Summarization, Detail Table, Detail Column

The Summarization column provides a list of aggregation options available (only 6) and if a particular option is not suitable in a field in the aggregate column (due to data type)  then it will be disabled

The Summarization drop-down in the Manage aggregations dialog offers the following values:

  • Count
  • GroupBy
  • Max
  • Min
  • Sum
  • Count table rows


As you can see there is no aggregation for average.  
Any advise if average can be used as a aggregation in the managed aggregation table for a direct query? Is there a work around that I am not seeing at the mokent?

 

Within summarization:

 

The article link you provided does not discuss other commonly used aggregations such as average that are not listed as options.
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-aggregations

Any information acknowledging that not all types of commonly used aggregations are available would be helpful.

Thanks 
Akthar