Average IF in DAX

Itayc

New Member
Joined
May 15, 2018
Messages
2
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

Active Member
Joined
Apr 30, 2014
Messages
256
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?
 

macfuller

Active Member
Joined
Apr 30, 2014
Messages
256
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"
 

Forum statistics

Threads
1,084,826
Messages
5,380,129
Members
401,648
Latest member
Pgill107

Some videos you may like

This Week's Hot Topics

Top