cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
oll Frequent Visitor
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

 

Thanks in advance!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Vvelarde Super Contributor
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
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Sean Super Contributor
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...

Calculating Average.gif

Hope this helps! Smiley Happy

 

 

View solution in original post

3 REPLIES 3
Vvelarde Super Contributor
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
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Sean Super Contributor
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...

Calculating Average.gif

Hope this helps! Smiley Happy

 

 

View solution in original post

mschave Visitor
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.01BI_Excel.PNG

 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,231)