Charting of Averages Not Working (As I expected)

[ballr]

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? 

[Greg_Deckler]

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.

[ballr]

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? 

[CahabaData]

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??

[ballr]

I appreciate the sanity check - and nothing is beneath trying 🙂

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.

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

[v-shex-msft]

Hi ballr ,

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

Below is my sample:

Source table.

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()))

 

Create a table visual:

 

Sum of rating:

Average of rating:

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

[CahabaData]

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....