topic Re: Average column value different in Power Bi Dax average function in DAX Commands and Tips

Average column value different in Power Bi Dax average function

Anonymous — Wed, 05 Jan 2022 09:15:27 GMT

My table looks like below in Excel sheet . used below Excel function to find the average value

=AVERAGE(A1:A12)

But same data set i have loaded into power bi . i have tired to average value using the below measure

2020 Avg = CALCULATE(AVERAGE('Current Forecast'[Actuals]))

But power bi average totally different

How to get same value in power bi . any idea .thanks in advance

Re: Average column value different in Power Bi Dax average function

ValtteriN — Wed, 05 Jan 2022 09:40:35 GMT

Hi,

Assuming you want to have the average from all of your data, you can just include ALL in your calculate.
E.g. 2020 Avg = CALCULATE(AVERAGE('Current Forecast'[Actuals]), ALL('Current Forecast'))

If you want to have some filter conditions you could use AVERAGEX or something like this:
2020 Avg = CALCULATE(AVERAGE('Current Forecast'[Actuals]), ALL('Current Forecast'),'Current Forecast'[Year]=2020)

I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!

Re: Average column value different in Power Bi Dax average function

Anonymous — Wed, 05 Jan 2022 09:49:04 GMT

ValtteriN i have tired above measure but its not working

Re: Average column value different in Power Bi Dax average function

Anonymous — Wed, 05 Jan 2022 10:10:09 GMT

ValtteriN i want only total sum value average . but my measure its convert all cell value to average .

Re: Average column value different in Power Bi Dax average function

ValtteriN — Wed, 05 Jan 2022 10:35:16 GMT

Hi,

I am not sure if I understood the issue, but based on you messages I assume you want to first summarize your data based on some dimension and then take averages out of these values?

In that case try something like this:

Start data:

Dax:

Summarized average = var vartable = SUMMARIZE(AverageExample,AverageExample[Category],"Value",SUM(AverageExample[Values])) return

AVERAGEX(vartable,[Value])

End result:

Re: Average column value different in Power Bi Dax average function

smpa01 — Wed, 05 Jan 2022 17:04:31 GMT

Anonymous it is simply not possible, POWER BI will give you exact same result as excel, unless there is something else you have not talked about.