cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ballr Frequent Visitor
Frequent Visitor

Charting of Averages Not Working (As I expected)

I am new to Power BI and most likely am missing something very basic.

 

I have Rating data that I am summarizing (average) by year.  Oddly, when I do this, I am getting  a very different value than what I get in my Excel analysis. Looking into the data (View Records) I see that only unique values are being aggregated (I have 8 values of 5 and 1 of 4 so it should be close to 4.9 but instead I am only seeing one record for 4 and one record for 5 so the average is 4.5).

 

Is there a reason Power BI is only viewing unique values? 

6 REPLIES 6
Super User
Super User

Re: Charting of Averages Not Working (As I expected)

Hmm, can you post some sample data and any calculations so that we can recreate? It sounds like there is a "DISTINCT" going on somewhere in the calculations. Can you walk me through how you are "View Records" of the data? Are you talking about going to the data model and looking at the records there? If that is the case, then something in your query is filtering out your records.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


ballr Frequent Visitor
Frequent Visitor

Re: Charting of Averages Not Working (As I expected)

smoupre,

Thanks for the quick reply. I have posted some sample data below for your to try to recreate the issue - although when I manually entered the data in a new model, it worked for me - so I fear it is maybe tied to a relationship that I established or a filter/distinct somewhere, as you mentioned.

 

I have removed all filtered data in the Query Editor as well as reviewed all of my custom measures/columns to ensure I don't have DISTINCT formulas.

 

 

 

You can see in the sample data below that the Average of Rating adds up to 4.5 when that is not the correct value.  Thoughts?Ratings.JPG 

 

CahabaData New Contributor
New Contributor

Re: Charting of Averages Not Working (As I expected)

hmmmm so how about a sanity check; you've probably done this but; with table visual highlighted - open the drop down for that field in the Visualization pane and select 'Don't Summarize' .... does the value go away? ... and then just for fun select 'Sum'... does that value add correctly??

www.CahabaData.com
ballr Frequent Visitor
Frequent Visitor

Re: Charting of Averages Not Working (As I expected)

I appreciate the sanity check - and nothing is beneath trying Smiley Happy

 

I selected 'Don't Summarize' and the data table dropped the average value (and added in a few values i force blank through a if statement (if =0 then BLANK()), as shown below.

 

 

Ratings.JPG

 

 

Then I select SUM of the data and while it does add it up, it doesn't do it correctly. See below:

Ratings2.JPG

 

 

CahabaData New Contributor
New Contributor

Re: Charting of Averages Not Working (As I expected)

is definitely grouped on Rating.  In your most basic core data table itself those values (4,5) are existing on each row?  I ask because it could instead be a join to a rating table so that 4 & 5 only actually exist twice; though appearing on each row in the displayed visualization....

www.CahabaData.com
Highlighted
Community Support Team
Community Support Team

Re: Charting of Averages Not Working (As I expected)

Hi ballr ,

 

According to your description, I try to build a table as you display.

 

Below is my sample:

Source table.

Capture.PNG

 

Dax:

Table = SELECTCOLUMNS( SUMMARIZE(Sheet1,Sheet1[Year],Sheet1[RatingDesc],Sheet1[Type Calc],Sheet1[Rating]),"Year",[Year],"RatingDesc",[RatingDesc],"Type Calc",[Type Calc],"Rating",IF([Rating]<>0,[Rating],BLANK()))

 

 Capture2.PNG

 

Create a table visual:

 Capture3.PNG

 

 

Sum of rating:

Capture4.PNG

Average of rating:

Capture5.PNG

The visual works well on my side.

 

Since I can’t reproduce your issue, could you upload the pbix file to OneDrive and share us a link?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |