I have to calculate average by country

[pinkukumar]

Hi,

I have the below table and from this I have to calculate the average rating for products from Rating 1 & Rating 2-

Product	Rating 1	Rating 2	Country
A	3	4	India
B	4	5	India
C	5	4	India
D	4	3	Australia
E	3	5	Australia
F	5	3	UK
G	4	4	UK
H	3	4	UK

After this, I have to calculate average rating by country from rating 1 & rating 2 and show this in bar chart.

Can anyone please help?

Thanks in Advance.

[Anonymous · Accepted solution]

Hi @pinkukumar ,
In power query editor, make a column calculating the average of rating 1 and rating 2 (name it Ratings Average) and then group by the country using the average of Ratings Average column we just created. 

You can use this M query in the advanced editor 

let
    Source = **Insert your Source file here**,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"_1", type text}, {"_2", type text}, {"_3", type text}}),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Changed Type1", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Product", type text}, {"Rating 1", Int64.Type}, {"Rating 2", Int64.Type}, {"Country", type text}}),
    #"Inserted Average" = Table.AddColumn(#"Changed Type2", "Average", each List.Average({[Rating 1], [Rating 2]}), type number),
    #"Grouped Rows" = Table.Group(#"Inserted Average", {"Country"}, {{"Country Average", each List.Average([Average]), type number}})
in
    #"Grouped Rows"

be sure to put the location of the source file in your system.

Then use this table to make the bar chart.

The output looks like this

The .pbix is attached here for reference.
https://nicesoftwaresolutions1-my.sharepoint.com/:u:/g/personal/ameshram_nicesoftwaresolutions_com/E...

Regards,
Aditya

[amitchandak]

@pinkukumar , Few ways 

Rate 1 = Average(Table[Rating 1])

Rate 2 = Average(Table[Rating 2])

Rate = AverageX(Table,(Table[Rating 1] + Table[Rating 2]) /2)