How to make % frequency chart in dashboard (reasons for QC failure)
Hi,
I am trying to make a chart in my dashboard that displays the reasons for QC failure as a percentage of overall frequency.
The input is from a sheet with a column as shown below. You can select the mode of failure from a dropdown menu, and can select multiple items if there are multiple modes of failure.
"In Dashboard" shows the output that I want in my dashboard. A simple Tchart with mode of failure and % frequency
Thank you for your help!!! I really need this answered within the next two days
Best Answer

You'll want to create your calculations in a separate Metrics sheet first. Then you can either use Metric Widgets or Chart Widgets to display this on a Dashboard.
I think the easiest way to do this would be to have 3 columns: Mode of Failure / COUNT / % Frequency
Then we can use a crosssheet COUNTIF statement to count the number of times a specific value is selected in that MultiSelect list. That will display in your COUNT column.
Next to it, you can have a different formula that then calculates the the percentage from this COUNT and the Total  either the total of all selections (ex. 2 selections in 1 cell equals 2) or the count of all rows (ex. 2 selections in 1 cell equals 1).
COUNT Formula
Since your column is a multiselect column, you'll need to use the HAS function within your COUNTIF. Try this:
=COUNTIF({Assembly Notes}, HAS(@cell, [Mode of Failure]@row)
This presumes your column name in this helper metric sheet is Mode of Failure, and the reference in {these} will need to be your Assembly Notes column in the other sheet.
Percentage Formula
Once you have a Count peritem, then you can use this to create your percentage. You can either use a SUM formula from within this metrics sheet to calculate the total number of selections, or a COUNT formula to the source sheet if you're looking to count rows (versus total values in cells).
Example:
=[Count Column]@row / SUM([Count Column]1:[Count Column]10)
This presumes you put the previous COUNTIF formula in a column titled Count Column, and that your grid is 10 rows long (since the SUM function is summing the data from row 1 to 10). You can adjust these numbers depending on how many rows you have in this chart.
What you do with this information on a Dashboard is then up to you! Let me know if you need help with any of the widgets and referencing this data.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Answers

Any insights would be hugely helpful!

You'll want to create your calculations in a separate Metrics sheet first. Then you can either use Metric Widgets or Chart Widgets to display this on a Dashboard.
I think the easiest way to do this would be to have 3 columns: Mode of Failure / COUNT / % Frequency
Then we can use a crosssheet COUNTIF statement to count the number of times a specific value is selected in that MultiSelect list. That will display in your COUNT column.
Next to it, you can have a different formula that then calculates the the percentage from this COUNT and the Total  either the total of all selections (ex. 2 selections in 1 cell equals 2) or the count of all rows (ex. 2 selections in 1 cell equals 1).
COUNT Formula
Since your column is a multiselect column, you'll need to use the HAS function within your COUNTIF. Try this:
=COUNTIF({Assembly Notes}, HAS(@cell, [Mode of Failure]@row)
This presumes your column name in this helper metric sheet is Mode of Failure, and the reference in {these} will need to be your Assembly Notes column in the other sheet.
Percentage Formula
Once you have a Count peritem, then you can use this to create your percentage. You can either use a SUM formula from within this metrics sheet to calculate the total number of selections, or a COUNT formula to the source sheet if you're looking to count rows (versus total values in cells).
Example:
=[Count Column]@row / SUM([Count Column]1:[Count Column]10)
This presumes you put the previous COUNTIF formula in a column titled Count Column, and that your grid is 10 rows long (since the SUM function is summing the data from row 1 to 10). You can adjust these numbers depending on how many rows you have in this chart.
What you do with this information on a Dashboard is then up to you! Let me know if you need help with any of the widgets and referencing this data.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 123 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!