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

Noah Webster

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

Genevieve P.

Hi @Noah Webster

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

Noah Webster

Any insights would be hugely helpful!

Genevieve P.

Hi @Noah Webster

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