topic Re: Average of Sum of Variables in DAX Commands and Tips

Average of Sum of Variables

CUdatanerd — Tue, 17 Dec 2024 16:22:34 GMT

I'm at a loss:

The issue is that sometimes a variable will have no value, dividing by 6 does not calculate the way I need it to when filtering to row values that have nothing in a column:

Any suggestions or help would be greatly appreciated!

Re: Average of Sum of Variables

mark_endicott — Tue, 17 Dec 2024 17:01:38 GMT

CUdatanerd - There are two ways you could solve this. You could go back to your model and Unpivot your data so that all of your "Assist" measures are in a single column. This would make this DAX and any future much easier.

Or you can use DAX. If you wish to do this, try:

AVERAGEX( 'Table', [MSC Assist] + [NL Assist] + [NM Assist] + [NP Assist] + [RE Assist] + [Tran Assist] )

I do not know enough about your model, or what you are trying to acheive visually to be confident that this will work for you.

If it gets you closer, please accept as the solution to help with visibility for others with the same challenge.

Re: Average of Sum of Variables

CUdatanerd — Tue, 17 Dec 2024 17:35:36 GMT

Each measure is an average of a column on various survey types; so 6 different tables.

Re: Average of Sum of Variables

Anonymous — Wed, 18 Dec 2024 01:49:31 GMT

Hi mark_endicott ,thanks for the quick reply, I'll add more.

Hi CUdatanerd ,

Try this

Assist In Future = VAR _m1 = [MSC Assist] VAR _m2 = [NL Assist] VAR _m3 = [NM Assist] VAR _m4 = [NP Assist] VAR _m5 = [RE Assist] VAR _m6 = [Tran Assist] VAR _table = {_m1,_m2,_m3,_m4,_m5,_m6} RETURN DIVIDE(SUMX(_table,[Value]),COUNTROWS(FILTER(_table,[Value] <> BLANK())))

Best Regards

Re: Average of Sum of Variables

mark_endicott — Wed, 18 Dec 2024 09:15:17 GMT

CUdatanerd - In that case my approach will not work, but Anonymous 's approach will.

HOWEVER - You should be very careful with this approach. In general, simply averaging averages is going to give a false answer. Please read this blog, which explains everything you need to know: https://www.stevefenton.co.uk/blog/2020/02/can-you-average-averages-in-your-analytics/

Essentially, if any survey has less responses than the others, it will skew the results of your final average.

Personally, I would see if it possible to append the results of the 6 surveys together into one table using Power Query, you will then be able to calculate an average over a singular column and the affects of any skew will be nullified.

Here's the documentation for appending: https://learn.microsoft.com/en-us/power-query/append-queries

Re: Average of Sum of Variables

mark_endicott — Mon, 23 Dec 2024 10:50:01 GMT

CUdatanerd - did we resolve your issue? If we did, please select the appropriate solution, it helps with visibility for others and for SuperUsers to keep their status! Thanks!