The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
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.
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?
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