Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
cmud
New Member

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

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:

 

cmud_0-1712694427581.png

 

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

1 ACCEPTED SOLUTION
jgeddes
Super User
Super User

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

jgeddes_0-1712696201942.png

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

1 REPLY 1
jgeddes
Super User
Super User

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

jgeddes_0-1712696201942.png

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!