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 T-chart 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 cross-sheet COUNTIF statement to count the number of times a specific value is selected in that Multi-Select 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 multi-select 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 per-item, 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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
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 cross-sheet COUNTIF statement to count the number of times a specific value is selected in that Multi-Select 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 multi-select 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 per-item, 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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!