Calculating customer review data

Question

Hi,&nbsp;I am importing customer reviews into PowerBI.&nbsp; They are in a format with one row per review.I need to do some extra calculations to work out our ratings over a specific period.&nbsp;For example, say in August we got 5 reviews. The data looks something like this:&nbsp;Customer ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Service Rating&nbsp;&nbsp;&nbsp; Review&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Review Date1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; it was great&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 01 Aug 20222&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; great service&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 04 Aug 20223&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; very happy&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10 Aug 20224&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; not great&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15 Aug 20225&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NULL&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; perfect&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25 Aug 2022&nbsp;To work out our star rating, the calculation is:&nbsp;= (&nbsp;&nbsp; (NUMBER OF 5 STAR REVIEWS * 5) +&nbsp;(NUMBER OF 4 STAR REVIEWS * 4)&nbsp; +&nbsp;(NUMBER OF 3 STAR REVIEWS * 3) +(NUMBER OF 2 STAR REVIEWS * 2) +(NUMBER OF 1 STAR REVIEWS *1) )&nbsp;&nbsp;&nbsp;DIVIDED BY TOTAL REVIEWS&nbsp;I would then add a month/quarter/year slicer to whatever visualisation I make to show this so that it is accurately calculated for whatever period I choose, as there is a lot of data over several years.&nbsp;I don’t really have any idea where to start with this – I’m quite a beginner in PowerBI, and not sure if I need to make new columns, new measures, or do it in a visualisation.&nbsp;One other issue is that not all customers leave a service review, so they would be removed from the calculation above entirely. Any advice on where to start would be much appreciated!

amitchandak · Answer

Jenjen , I think you can simply do a new measure 
&nbsp;
Average(Table[Service Rating])
&nbsp;
where Service Rating is a number. Avg should not consider blank
&nbsp;
else try measure
&nbsp;
calculate(Average(Table[Service Rating]), filter( Table, not(isblank(Table[Service Rating])) ) )

ashish_mathur · Answer

Hi,
You say that you have one row per review but it looks like you have one row per customer.&nbsp; Share a larger dataset (in a format that can be pasted in an MS Excel file) and show the expected result.&nbsp;

Forum Discussion

Calculating customer review data

2 Replies

Related content

DAX help calculating Repeat Customers

Review Custom formulas

Review custom visual

MoM calculation

Customer Scorecard