Visualization with Feedback

[Kalachuchi]

I have a table which has the following data.

FeedbackProviderName	Week	CustomerService.1	CustomerService.2	CustomerService.3	OverAll
Jewell Maddox	1	Sometimes	Sometimes	Rarely	Sometimes
Rolland Gerry	3	Usually	Usually	Rarely	Usually
Kalyn Tara	4	Almost Always	Usually	Usually	Usually

for each rating there is a corresponding rate

Frequency	Rate
Almost Always	5
Usually	4
Sometimes	3
Rarely	2

I need to have a column chart with y axis as my frequency and x axis is Week column getting the average overall column per week

[v-robertq-msft · Accepted solution]

Hi, @Kalachuchi 

According to your description, you want to get a column chart with the y-axis as my frequency, and the x-axis is the Week column with the average of value per week. I think you should do some transformation on your data in the Power query first, you can follow my steps:

1. Select the three columns and Unpovit them:

2. Select the first table and merge like this:

This is the M query in the advanced editor, you can copy and paste into the advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kotT83JUfBNTEnJr1DSUTIE4uD83NSSzNzUYjR2UGJRak4limCsTrRSUH5OTmJeioJ7alERSNYYiEOLSxNzwGoRLLh2mBBIs3diTmWeQkhiUSJQwgSIHXNy84tLFBxzyhMri3GYBDcgFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FeedbackProviderName = _t, Week = _t, CustomerService.1 = _t, #" CustomerService.2" = _t, #" CustomerService.3" = _t, OverAll = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FeedbackProviderName", type text}, {"Week", Int64.Type}, {"CustomerService.1", type text}, {" CustomerService.2", type text}, {" CustomerService.3", type text}, {"OverAll", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"FeedbackProviderName", "Week", "OverAll"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Frequency"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"OverAll"})
in
#"Removed Columns"

And you can get a table like this:

3. Then create a column chart and place it like this:

And you can get what you want.

You can download my test pbix file here

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[amitchandak]

@Kalachuchi , Unpivot the first table

https://radacad.com/pivot-and-unpivot-with-power-bi

Join with the second table(rating) hope 1-M  join

create a new measure

averagex(Table, related(rating[Rate])