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
Anonymous
Not applicable

Average Not Returning Correct Number

I have this table:

20.PNG 

 

So the highlighted number is supposed to be the average of that column. However, if I export the results and highlight the entire column from top to bottom I get a totally different number:

21.PNG

 

The number in the spreadsheet is what I'm looking for but I don't know how or why PBI is returning this random number that doesn't add up in the first screenshot? It could be because of grouping but I'm really not sure.

1 ACCEPTED SOLUTION

@Anonymous 

you will need to make a measure with a Averagex.
If you only use the aggregator in your Deal Size column, the average will not work for the total amount.
It would look something like this: 

Measure = Averagex ( Values ​​(table [Producer]), CALCULATE Sum (table [Deal Size]) ) 

 

 



Sorry about my English.

 

 

View solution in original post

6 REPLIES 6
EugenioJunior
Frequent Visitor

@Anonymous 

You are trying to use averagex ?

Anonymous
Not applicable

@EugenioJunior  No, I took a column and right clicked and clicked Average. I see what it's doing, it's taking the sum of the column that I'm averaging and calculating the average based on the # of times a person is in the table:

1.PNG

 

So it's taking 1,132,185.06/735 to get $1,540.39. But I don't know how to get it to NOT do that since it's doing it by default. I just want it to average the values on the right hand column without taking into account any other table info so it properly returns the correct value. I don't want it to divide by any counts, just the flat out average of the numbers in the column itself.

@Anonymous 

you will need to make a measure with a Averagex.
If you only use the aggregator in your Deal Size column, the average will not work for the total amount.
It would look something like this: 

Measure = Averagex ( Values ​​(table [Producer]), CALCULATE Sum (table [Deal Size]) ) 

 

 



Sorry about my English.

 

 

Anonymous
Not applicable

Hello @EugenioJunior , 

 

I have similar (or the same problem) I'm not sure...

 

But, Why we should use SUM function if we want to calculate the AVERAGE?

 

I'm understanding that if I use VALUES (table["grouping_value") it in order to have the group of rows as 1 block.

 

In the example Producer.

 

Kim Williams 211.67

Jill Salisbury 278.76 

 

In my case I have the following situation:

Site and value

Site1 = 71.1

Site2 = 47.5

Site 3 = 51.4

Average that I want => 71.1+47.5+51.4 = 170 / 3 = 56.67

But the showed total is 53.9.

 

I try Measure = AVERAGEX (Values/Table[site];Calculate ( AVERAGE(table[value]) But the outcome is not that I want.

 

Could you help me?

 

Best regards.

 

 

 

 

 

 

 

Anonymous
Not applicable

@EugenioJunior  I take that back, you were correct! My DAX wasn't. My apologies.

Anonymous
Not applicable

@EugenioJunior it returned the same result of what I already had Smiley Sad

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.