Forum Discussion
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 see the measures fields coming up in the details column:
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 tableavg pricepaid 2 = AVERAGE(ProperyDetails[PricePaid]) - does not hit the aggegated table.
3 Replies
Anonymous , check the data type of column. Also close Power Bi and open and try again.
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-aggregations
In case you still do not see it. create an issue -https://community.powerbi.com/t5/Issues/idb-p/Issues
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 disabledThe 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
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 tableavg pricepaid 2 = AVERAGE(ProperyDetails[PricePaid]) - does not hit the aggegated table.
