AVERAGE IF function DAX

[oll]

Hey,

Wonder if there is a way to make a calculated measure to calculate the average of all numbers that belongs to a certain category.

For example, if there is a DAX expression to calculate the average of all numbers belonging to Computers:

7          Computers

7          Cell phones

7          Gaming consoles

6          Computers

5          Cell phones

10        Gaming consoles

11        Computers

5          Cell phones

2          Gaming consoles

Thanks in advance!

[Vvelarde · Accepted solution]

@oll

Hi, a way to obtain this is:

AverageNumberofComputers=Calculate(average(Table[ColumnNumbers]),Filter(Table, Table[ColumnName]="Computers"))

Let me know if you need more help

[Vvelarde · Accepted solution]

@oll

Hi, a way to obtain this is:

AverageNumberofComputers=Calculate(average(Table[ColumnNumbers]),Filter(Table, Table[ColumnName]="Computers"))

Let me know if you need more help

[stefanot]

can you please clarify how you'd use the same formula

AverageNumberofComputers=Calculate(average(Table[ColumnNumbers]),Filter(Table, Table[ColumnName]="Computers"))

but instead of filtering by a defined value ("Computers") to filter it by the each single line value of the "[ColumnName]" column?

[Sean · Accepted solution]

If you want to calculate the average only for 1 category at a time go with the above method

However if you have MANY more categories you can create a simple average measure and use it in a Matrix

Place category in the Rows and that Measure will be sliced per Category

Average Measure = AVERAGE ( 'Table'[Value] )

Even easier you can just drag the value column again to the Values area - right click and change the aggregation - as shown below...

Hope this helps!

[yassineanas]

so helpful really thanks 😊

[mschave]

What if you didn't necessarily want to create a matrix, but rather a new column? For example, I'm working with position-level data (granular) but I need to calculate weighted-average (not simple averages) analytics that are grouped by various categories like Industry. So to start, I have a column that shows the weight of each position relative to the dataset as a whole, but I need to find the weight of each position relative to that position's industry. 

In excel, I did it like this:

=AVERAGE(IF($B$2:$B$229=$G2, $C$2:$C$229))*0.01