Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Berti10
New Member

Quick measure to aggregate average and totals

Can you please help me create a quick measure for case below?

I have a data set containing product reviews coming from a website of Internet store. These are posted by customers who select the rating from 1 to 5 
In data set there is product code, product category, date of review and product rating

I would like to aggregate this data to show

- what was the total number of reviews at the end of each quarter

- what was the average score end of each quarter

So each calculation takes into account the previous periods


the default quick measures I used in PowerBI only showed totals and averages for each quarter but didn't aggregate them
then also I'd like to filter by categories 

here is a data sample and expected result
data sample.jpgexpected result.jpg

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

Create a Calendar Table with calculated column formulas for Year, Month name, Month number and Quarter.  Sort the Month name column by the Month order.  Create a relationship (Many to One and Single) from the Review Date to the Date column of the Calendar Table.  To your visual, drag Year and Quarter from the Calendar Table.  Write these measures

R = average(data[rating])

Average rating since inception = calculate([r],datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date])))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Berti10 ,

I'm sorry, but I need to confirm something with you before I can better help you find a solution.
How is the review calculated? In the datasheet you give, does each row of records represent a review? I can't derive your calculation based on the example data you gave.
In the meantime, how is the average rating calculated? According to the dataset you provided, shouldn't the average rating of 2022 Q3 be (3+3+4)/3=3.33? Is it the reason for the incomplete data set?

vjunyantmsft_0-1699924917859.pngvjunyantmsft_1-1699924931776.png
And what is the current calculation? I guess it's a summary of the data from the beginning of 2023 Q3 to today?

Best Regard
Dino Tao

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors