Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
@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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
48 | |
24 | |
20 | |
14 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |