Average IF in DAX

[Itayc]

Hey,

What I need is to make an average of all the numbers under heading A (there are 3 in the example), but the calculation will be by line rather than column.
For example: At Sarah's row, the new column will sum up the numbers 5+10+10 because they are under a header that contain the letter "A", and then will be divide by 3.
Final result will be 8.33 for Sarah's team.
[FONT=&quot]
[/FONT]

Thanks for helping!

 

[macfuller]

My browser won't show the image so I can't tell exactly what's happening, but you might want to consider doing this in Power Query. Do the heading names change? If there are n columns with "A" but a row value of a particular column has no value would your average be based on n or n-1?

 

[Itayc]

My browser won't show the image so I can't tell exactly what's happening, but you might want to consider doing this in Power Query. Do the heading names change? If there are n columns with "A" but a row value of a particular column has no value would your average be based on n or n-1?

On n-1, here is an URL to the image:

http://www.interload.co.il/upload/5611759.PNG

 

[macfuller]

That doesn't quite answer which way you want the calculation to go. However, as a possibility...

In PowerQuery unpivot the columns that have a letter with values that you want to average (e.g. all the A columns). This assumes that the column headers don't change. If you always want n columns then change any null values to zero before unpivoting, otherwise the null values will not unpivot and you'll get n-x values. Let's say you call the resulting column "Category".
If the Categories you want always start with the letter of interest then write a measure to average across the team where LEFT(MySourceTable[Category], 1) = "A"