cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Power Participant
Power Participant

Avg Total

Hello  -  I have the average of opportunties (from our sales team) in a table visual.    The total (presumably the average) shows 175.44  but if I export the table to Excel, the average shown in Excel is 181.    (note, you are not seeing the full data here).  

 

This column is showing the opportunity age of each (not the average, but the actual days elapsed).    I created another column, not shown, using this formula, and it returns the same row values, and ends up returning the same total value as well.   

 

My question, is the Excel data correct...and is this an issue of the "measure total" not being right?

 

Avg Opp Age = AVERAGEX('AllOpps-Products','AllOpps-Products'[Days Since Sales Opp. Created])

 

texmexdragon_0-1600457051754.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

@texmexdragon 

 

If the total average isn't correct, it probably has to do with the filter context of the table/matrix and how the relationships are established in the model, and/or the measures involved.

So we need more information about the model, how the table/matrix is set up and any measures involved.

(You will probably solve this by using an AVERAGEX(SUMMARIZE...) but it may be overkill depending on the model structure and matrix filter context)

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

9 REPLIES 9
Highlighted
Solution Sage
Solution Sage

@texmexdragon 

It is Rounding issue. In Power BI Table increase the Decimals and then download to excel and let me know whether this issue resolved.

Memorable Member
Memorable Member

hi @texmexdragon  - What is the column formatted as in excel? also can you share the approx. number of records you have. 

As far as I am aware there is no issue with Totals in PowerBI - works fine in all visuals I have used so far. 

This could be a rounding issue in Excel though 175.44 to 181 seems a bit of a stretch but could happen if you have a large volume of data. 

 

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks! 

Highlighted
Super User III
Super User III

Hi,

Share the Excel file and show the discrepancy there very clearly.  Also, share the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted

@VijayP    @Ashish_Mathur    @Sumanth_23 

 

I do not think it is a rounding issue.   If I constrain the date range so just three records show, you can use a calculator to find the average of these three values would be 177.66.     But the table in Power Bi shows 165.76  using the formula below. 

 

Avg Opp Age = AVERAGEX('AllOpps-Products','AllOpps-Products'[Days Since Sales Opp. Created])

 

Ideally, not only should the total average be correct, it should be dynamic and adjust as the number of records does.   

 

texmexdragon_0-1600533272264.png

 

Highlighted
Super User II
Super User II

@texmexdragon 

It appears you are using AVERAGEX with a column as the expression. If you are averaging a column in a table, Just use the function AVERAGE.

otherwise, create an aggregation measure for the column values (like SUM) and use that in the AVERAGEX function. Something along the lines of :

AVERAGEX(Table, [Sum of values])





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Highlighted

@PaulDBrown 

 

Hi Paul  -  I tried your formula, and several others with and without AverageX.    

 

For the love of God I just want a simple average of the column!  😁       There are 32 records in this list.   4000 / 32  =  125   (but that is not the value I want to show).   So I can see what Power Bi is doing....it's jut not what I need it to do.  

 

I want to show the same value as what Excel shows....or what it will show if you just were to add up Avg Opp Age and divide by 6...which will be 108.66.....not 125.   

 

texmexdragon_0-1600540968150.png

 

Highlighted

@texmexdragon 

 

If the total average isn't correct, it probably has to do with the filter context of the table/matrix and how the relationships are established in the model, and/or the measures involved.

So we need more information about the model, how the table/matrix is set up and any measures involved.

(You will probably solve this by using an AVERAGEX(SUMMARIZE...) but it may be overkill depending on the model structure and matrix filter context)

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Highlighted

@PaulDBrown 

 

Ok, so discovered the "why", but not sure of the fix.  

 

The totals below will average on a calculator, or Excel, to 714.20   -   just as they do here.  

 

What made the difference was by adding "item" to the table.    For example, the first two lines below are from the same Opportunity  (from our CRM system)....but there are two distinct rows to this Opportunity, representing two differnent products that are part of the overall opportunity.    So....adding the "item" (product) to the table exposed this additional row, and made the average total add up correctly.   It was actually correct all along....just deceiving because the other rows were not showing.   And when exporting to Excel, the export only captured the rows that were showing. 

 

 

 

texmexdragon_0-1600544025295.png

 

Highlighted

Sorry, I should have edited this to say that no "fix" is really needed at this point.   

 

But your insight into the table and filter context is what led me to see what the issue was.  Thank you

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors