Correct way to average at different aggregate levels. Best Practice

[GlynMThomas]

Hi Guys,

What is the best practice in power bi for getting averages at different granularities?

E.g. If my data in power bi is at client account level and I want the average balance across accounts this would be straight forward as the data is already at account level. However if I wanted the average balance across the clients I would need to sum and group by the client id or name first then take the average.

Now normally I would create a summarised table using a group by to transform the data before taking the average at client level. However I'm just wondering if this is the correct way I should be doing it, and there's not a formula in dax I should use instead.

I'd be keen on your thoughts regarding this.

Kind Regards

Glyn

[GlynMThomas · Accepted solution]

Thanks guys,

It seems the way I'm already doing it works fine so I'll continue that way.

Thanks for you help.

Kind Regards

Glyn

[VSrujana]

You can use the DAX function All Except with Calculate i.e Calculate (Average(Balance),AllExcept(Client Name)).

[v-kelly-msft]

Hi @GlynMThomas ，

You can use averagex(summarize(table,column,"name",expression),column).

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

Did not get it completely

But Avg should preferably should be like

divide(Sum(a),sum(b))