Calculating customer review data

[Jenjen]

Hi,

I am importing customer reviews into PowerBI.  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.

For example, say in August we got 5 reviews. The data looks something like this:

Customer ID       Service Rating    Review                 Review Date

1                              5                             it was great         01 Aug 2022

2                              5                              great service      04 Aug 2022

3                              4                              very happy          10 Aug 2022

4                              2                              not great             15 Aug 2022

5                              NULL                     perfect                 25 Aug 2022

To work out our star rating, the calculation is:

= (   (NUMBER OF 5 STAR REVIEWS * 5) + 

(NUMBER OF 4 STAR REVIEWS * 4)  + 

(NUMBER OF 3 STAR REVIEWS * 3) +

(NUMBER OF 2 STAR REVIEWS * 2) +

(NUMBER OF 1 STAR REVIEWS *1) )   

DIVIDED BY TOTAL REVIEWS

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.

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.

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!

[Ashish_Mathur]

Hi,

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

[amitchandak]

@Jenjen , I think you can simply do a new measure

Average(Table[Service Rating])

where Service Rating is a number. Avg should not consider blank

else try measure

calculate(Average(Table[Service Rating]), filter( Table, not(isblank(Table[Service Rating])) ) )