Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
oll
New Member

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
Vvelarde
Community Champion
Community Champion

@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

View solution in original post

Sean
Community Champion
Community Champion

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

5 REPLIES 5
Vvelarde
Community Champion
Community Champion

@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

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
Community Champion
Community Champion

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

 

 

so helpful really thanks 😊

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.