Calculate Average Rating

[bahare76]

Dear colleagues,

I have a table that consists of ratings of 3 products. I wanted to present all the ratings on a single card in the PBI dashboard. I don't want to create three different measures for each product. You may refer the sample data and dashboard below. Appreciate your help.

Name	Rating on Product A	Rating on Product B	Rating on Product C
Allan	3	1	4
Raymer	4	4	5
Johnson	5	5	1
Donna	1	2	4

[Tahreem24]

@bahare76 If you want to do using measure so do like below:

Total Rating = SUM('Table'[Rating on Product A])

+SUM('Table'[Rating on Product B])

+SUM('Table'[Rating on Product C])

[bahare76]

Hi @Tahreem24 , what about average calculation?

[Tahreem24]

@bahare76 Write AVERAGE instead of SUM in above measure.

[bahare76]

@Tahreem24 

Sorry, perhaps I was not making myself clear. I actually wanted to show the Product's average rating on the card whenever I click each column chart. So, for instance, if I click Product A column chart, I will get its average. Similarly, for Product B and C.

[v-stephen-msft · Accepted solution]

Hi @bahare76 ,

Click on the product A column chart and then dynamically get the results of the card, there is no way.

But you can create a slicer and select different products in the slicer to get different results.

1.Create a seperate table.

2.Create a measure.

Average Rating =
SWITCH (
    SELECTEDVALUE ( 'Table (2)'[Prduct] ),
    "Product A", AVERAGE ( 'Table'[Rating on Product A] ),
    "Product B", AVERAGE ( 'Table'[Rating on Product B] ),
    "Product C", AVERAGE ( 'Table'[Rating on Product C] )
)

The result is as follows.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Thejeswar]

Hi @bahare76 ,

You can unpivot only the 3 product columns so that they apper as single column as shown below

The Table will look as below

You can use this value column in a simple card visual to get the Average across Products

[amitchandak]

@bahare76 , Unpivot the table, split the column on last space into two column and get Product

https://radacad.com/pivot-and-unpivot-with-power-bi
Split Column Power Query: https://youtu.be/FyO9Vmhcfag