topic Re: Average across multiple columns producing wrong number? in DAX Commands and Tips

Average across multiple columns producing wrong number?

jmscrx — Mon, 08 Jul 2024 20:52:41 GMT

Apologies in advance because I feel like I'm missing something obvious here, however I'm simply trying to write a measure that produces the average of the values across multiple columns (ultimately I'll have a slicer to display the resulting value based on month). For example, with data like this (illustration purposes only).

Value1	Value	Value3	Value3	Value5	Value6
5	10	20	30	40	50

And this formula

TotalAvg = (SUM('Table1'[Value1]) + SUM('Table1'[Value2]) + SUM('Table1'[Value3]) + SUM('Table1'[Value4]) + SUM('Table1'[Value5]) + SUM('Table1'[Value6])/6)

The output value isn't what I'd expect (26), it's a much higher number (in my real data the values for 1 row are 60, 44, 22, 33, 36, 37 and the output of the formula is producing 201.17 instead of 39).

Again, I feel like I'm missing something obvious here and have tried several things, and read several other "average across multiple columns" discussions to no avail.

Thanks!

Re: Average across multiple columns producing wrong number?

Irwan — Tue, 09 Jul 2024 01:08:38 GMT

hello jmscrx

your DAX looks good but you misplaced your bracket. This DAX means Value6 got divided by 6 then add to Value1 up to Value5.

tried your data with your DAX and the result is as you need.

Hope this will help you.

Thank you.

Re: Average across multiple columns producing wrong number?

jmscrx — Tue, 09 Jul 2024 14:33:48 GMT

Irwan - I knew it was something silly that I was missing. Updated my DAX and this is working now as expected.

THANK YOU!

Re: Average across multiple columns producing wrong number?

Irwan — Tue, 09 Jul 2024 22:11:05 GMT

hello jmscrx

glad to be a help.

Thank you.