Chart of Average Values Not Displaying Correctly

[ballr]

Long time listener, first time caller 🙂

I'm new to Power BI and have data that I am trying to summarize by average over a period of time (years). The values are not matching my excel analysis and in looking into why I found something peculiar:

The Chart seems to be rendering only unique integers in the average.  Most of the values are repeat so I am only getting a few observations into the average and getting very different values.

My columns are:

Year: 2010 - 2016

Rating: This is an integer value from 0 to 5, I used an if formula to force all 0 values to BLANK() as the data shows non-responses (blanks) as 0.  Cleaned data is 1 to 5.

Rating Type: This is the categorical equivalent of the Rating value. 

My test data is:

Year - all 2015

Ratings - 4, 5, 5, 5, 5, 5, 5, 5, 5

Rating Type - Very Good, Exceptional, ......

The Chart is showing 4.5 as the average when it should be closer to 4.9. When I view records it shows:

Rating 4, 5

Rating Type Very Good, Exceptional

Not sure what happened to the other 7 values but I must be doing something wrong.  I could really use some assistance. Thanks for the help!

[v-shex-msft · Accepted solution]

Hi ballr,

>> Not sure what happened to the other 7 values but I must be doing something wrong.

Your formula is correct, the ‘summarize function’ will ‘Returns a summary table for the requested totals over a set of groups.’. Since your records contain the duplicate data, the function merge the duplicate data to one records.

If you want to keep these records, you can try to add an column with unique values and add this column to your formula.

 

In addition, if you want to get the correct average value, you can use below code:(calculate the old table)

Measure = CALCULATE(AVERAGE(Sheet1[Rating]),ALL(Sheet1))

Regards,

Xiaoxin Sheng