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, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
PowerBINoob12
Helper I
Helper I

Help with simple clustered column chart (pics included)

Hi,

I'm trying to create a simple bar chart that measures the % of my test scores for each month 

This is the format i want to be able to show in PowerBI (I created the below in excel )

 

4af24559-6fd7-4342-92d0-7e0b05b37a52.png

 

This is how I have the data pullled into powerbi from a sharepoint site

 

Figures.PNG

This is as far as I have gotten :

PowerBINoob12_0-1664211770829.png

not sure what to do next so any guidance would be appreciated.

 

I think it might be down to the way i have the data entered on the table but im not sure.

Any advice would be appreciated.

1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

Hi @PowerBINoob12 ,

 

Approve with @parry2k .

Please try:

First, Unpivot the other columns:

vjianbolimsft_0-1664271587590.png

Output:

vjianbolimsft_1-1664271608407.png

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlTSUTI0MMBJxupEK7mlJhURqdQ3sSg5gwh1jgVFmTlEmUeMrV6lealYFViiqsohxjDH0vTS4hLCxgWnFpSk5ialFhFhpn9yST5xKv3yy4g11CU1GabUAo/KWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Maths = _t, English = _t, Spanish = _t, French = _t, Latin = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Maths", Int64.Type}, {"English", Int64.Type}, {"Spanish", Int64.Type}, {"French", Int64.Type}, {"Latin", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Month"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Then apply it to the visual:

vjianbolimsft_2-1664271695580.png

Final output:

vjianbolimsft_3-1664271709774.png

 

Best Regards,

Jianbo Li

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

View solution in original post

3 REPLIES 3
v-jianboli-msft
Community Support
Community Support

Hi @PowerBINoob12 ,

 

Approve with @parry2k .

Please try:

First, Unpivot the other columns:

vjianbolimsft_0-1664271587590.png

Output:

vjianbolimsft_1-1664271608407.png

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlTSUTI0MMBJxupEK7mlJhURqdQ3sSg5gwh1jgVFmTlEmUeMrV6lealYFViiqsohxjDH0vTS4hLCxgWnFpSk5ialFhFhpn9yST5xKv3yy4g11CU1GabUAo/KWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Maths = _t, English = _t, Spanish = _t, French = _t, Latin = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Maths", Int64.Type}, {"English", Int64.Type}, {"Spanish", Int64.Type}, {"French", Int64.Type}, {"Latin", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Month"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Then apply it to the visual:

vjianbolimsft_2-1664271695580.png

Final output:

vjianbolimsft_3-1664271709774.png

 

Best Regards,

Jianbo Li

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

parry2k
Super User
Super User

@PowerBINoob12 you need to unpivot your data and then it should be easy:

 

Transform Data -> Select Month Column -> Right CLick -> Unpivot other column

 

It will add two new column Attribute and Value, rename these columns as you see fit. Close And Apply

 

In a visual, put attribute on x-axis, month on legend and count on values (y-Axis) and that will do it.

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thanks for the guidance so far but I think there may be an issue with what is populated in attribute now?

PowerBINoob12_0-1664267522305.png


It also wont let me add more than one item to the Y-Axis:

PowerBINoob12_1-1664267619590.png

 


Any ideas?

Thanks.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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