Progress bar calculation and design

[Anonymous]

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! 

[parry2k · Accepted solution]

@Anonymous 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

[Anonymous]

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]

@Anonymous 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/

[Anonymous]

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]

@Anonymous is this what you are looking for?