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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PBI_User_2023
Helper I
Helper I

Progress bar calculation and design

PBI_User_2023_0-1717622011467.png

The table format is as shown above. I have a measure for % calculation which is sum of 1s divided by the total number of rows. Each Category is supposed to have a different % as shown. I want to show much work is done in Cat 1, 2, 3 and 4. Through a matrix visual, I am able to get a tabular/matric form. Not sure how to get to showing the horizontal stack bars where Cat 1-4 are on Y axis and total 100% and % work done is shown on the X-axis. 

 

Thanks for any help! 

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@PBI_User_2023 I see the issue, your data is in pivoted shape, and you need to unpivot it.

 

Transform Data -> Select Id Column -> Right click -> Unpivot other columns, it will create two columns attribute and value, rename those as you fit and then use attribute columns (or whatever you rename it to) on the y-axis.

 

HTH



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.

View solution in original post

5 REPLIES 5
parry2k
Super User
Super User

@PBI_User_2023 I see the issue, your data is in pivoted shape, and you need to unpivot it.

 

Transform Data -> Select Id Column -> Right click -> Unpivot other columns, it will create two columns attribute and value, rename those as you fit and then use attribute columns (or whatever you rename it to) on the y-axis.

 

HTH



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.

The example I gave above is simplified. I have hundreds of columns and that table is also joined to another table 1:1. When I tried to unpivot the way you recommended, it threw an error as the 1:1 relationship was breaking due to creation of the Attribute. So here's what I did and please let me know if it makes sense.

I duplicated the original table and removed the 1:1 relationship with this other table. The original table still has that relationship to do the other logic that I have elsewhere in the report. This new table without any relationship is my table for unpivoting. I clicked on ID and did "unpivoting other columns" as you suggested. For the bar charts, I brought in the percentages of work done and "total 100%" measures the way you suggested, but I am using these from the original table. The new duplicated table now unpivoted doesn't have those measures. For the Y axis, I am using the Attribute column from the duplicated, unpivoted table. 

Does it make sense? If it does, if the report is refreshed, does the duplicated table automatically update its values based on the main table? 

 

Thank you for the insights! 

parry2k
Super User
Super User

@PBI_User_2023 if that is what you want trick is to add another measure let's call it % 100 and change the format of this measure to %

 

% 100 = 1

 

create a cluster bar chart, add category on y-axis and % count and % 100 measure on the x-axis, and the change the layout settings to overlap as shown below/

 

parry2k_0-1717625893182.png

 



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.

Hi Parry2K, 

Thank you for your prompt response. This is working great only for 1 of my 4 categories. I am confused about the Attribute field that you have on the Y-axis. I don't have a parent field for each of my 4 category columns. Cat1, Cat2, Cat3 and Cat4 are distinct and mutually exclusive categories and they appear as columns in the underlying dataset. Your blue/blue bar chart looks great though. That is what I am looking for. Can you explain the Attribute column and how to get Cat1, Cat2, Cat3 and Cat4 to appear on Y-axis all at the same time as seen in your chart? 

parry2k
Super User
Super User

@PBI_User_2023 is this what you are looking for?

 

parry2k_0-1717625287527.png

 



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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors