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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.