cancel
Showing results for
Did you mean:
oll Frequent Visitor

## AVERAGE IF function DAX

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Vvelarde Super Contributor

## Re: AVERAGE IF function DAX

@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

Lima - Peru

Proud to be a Datanaut!

Sean Super Contributor

## Re: AVERAGE IF function DAX

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! 3 REPLIES 3
Vvelarde Super Contributor

## Re: AVERAGE IF function DAX

@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

Lima - Peru

Proud to be a Datanaut!

Sean Super Contributor

## Re: AVERAGE IF function DAX

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! mschave Visitor

## Re: AVERAGE IF function DAX

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 Announcements   