Creating a bar chart with a weighted average of 5 other columns

[cmud]

Hello, I've a SharePoint list with a set of questions showing a ranked list of choices (first through fifth) that I'd like to combined into a "weighted" average.  Sample data is below:

Rank scoring would be opposite of the rank (1st tier is worth 5, 2nd tier is 4, etc.)  As best I was able to figure it out, the measure could be calculated as:

((COUNTA('Motivation'[Motivation1])*5)) + ((COUNTA('Motivation'[Motivation2])*4))

+ (COUNTA('Motivation'[Motivation3])*3)+ (COUNTA('Motivation'[Motivation4])*2) + (COUNTA('Motivation'[Motivation5])*1))

So there's my measure.  Now for my problem.  I was able to put that on my Y-axis, but I couldn't get an appropriate X-axis.  Putting any specific motivation on the X-axis (used just for the motivation names) limited the numbers to JUST that one set of data.  I attempted to create stacked charts, etc. with each of the 4 Motivations on top of each other, and I couldn't make it work.

I'm not sure what I'm missing.  I think it's possible to put the result of that measure into a table, but I'm quite new at PowerBI and don't know enough DAX to make it happen; I've scoured the forum and can't quite make the examples here work.  Alternatively, if it's not in an extra table, I'm not sure if there's a way to just make it work inside the visualization itself.

Any help would be greatly appreciated.

Thank you very much

Clint

[jgeddes · Accepted solution]

If you are looking for a result similar to...

Your best bet would be to unpivot the motivation columns and assign the scoring in Power Query before building your visuals.
Access Power Query by clicking the 'Transform data' button on the Home ribbon.
Hope this gets you pointed in the right direction.