Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |