# How to make % frequency chart in dashboard (reasons for QC failure)

Options

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

• Employee
Answer ✓
Options

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

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

## Answers

• Options

Any insights would be hugely helpful!

• Employee
Answer ✓
Options

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

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!