Counting Unique Values within a Cell and Using Two...

dcadwallader · ‎03-22-2022

I have a report that shows Contractor Performance on Inspections; one inspections consists of an assessment of eleven measures. In total, across those eleven measures, there are fifty-six possible points of failure or "fails".

On this report I have two slicers, that allow me to drill down by Inspection Date and by Contractor. In other words I can look at the performance of one or more contractors over various periods of time both as whole and across all eleven individual measures.

What I am looking to do, is develop the report so that I can chart the top fails, per measure, per contractor and of course, per time period.

Taking Measure 1 as an example, there are ten possible fails in Measure 1 and these will always be returned in column CN. So, in the attached, APP0031 has failed one item in Measure 1 and this is recorded in cell BN32. However, APP0191 has failed two items and as such two items are recorded in cell BN192.

The report I am looking to build would be able to count the individual number of times each of the ten possible fail items for Measure 1 appears in column CN and allow this number to work with the existing slicers.

Is this doable?

Link to file - https://ksnenergy-my.sharepoint.com/:x:/g/personal/dcadwallader_ksnenergy_ie/EbH_X3-2kgFLpR55TR0QoLQ...

KNP · ‎03-22-2022

@dcadwallader - did you try to attach something? I can't see a link to anything.

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.