Average of Sum of Variables

[CUdatanerd]

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!

[mark_endicott · Accepted solution]

@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. 

[mark_endicott]

@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!

[Anonymous]

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

[mark_endicott · Accepted solution]

@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. 

[CUdatanerd]

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

[mark_endicott · Accepted solution]

@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