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
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
Help Article Resources
Categories
Check out the Formula Handbook template!