Finding Minimum of Grouping and returning a conditional value

Question

Good day all, I really appreciate you taking a look at this requirement.

Here's the issue:

Let's say we have two columns, Column A contains a coded ID, Column B has a percent complete.

Each of these items may or may not have an 'extension' past the alphabetical suffix (I1234.AAA.9999) but those are to be grouped in with the 'others.' In other words, I1234.AAA is one object, E5678.BBB is another, and C4321.CCC is yet another. I need to count objects that are 'all the way' done/not done.

What I was thinking of is somehow leveraging the MINIMIUM but I need some enlightenment. Using the example, two of these objects are incomplete. The end goal would be to have a single cell counter that says '2'. I've been outputting each measure as its own pivot table to arrange these types of counters in a worksheet.

Thanks everyone, any ideas are welcome.

Answer 1

You will need to remove the alphabetical suffix from the data (for example, you can have 2 columns - one with the suffix, and the other without). Usually, it's done either at the source, or in Power Query.

You can also use DAX and create a calculated column, let's call it "Object", like this:

Object = LEFT(TableName, 9)

The formula assumes that all your objects have 9 symbols. If it's not true, you'll need more elaborate code to remove the suffixes.

Next, create a measure to find min completion:

Min Completion = MIN(TableName[Column B])

Next, create a measure to count incomplete objects:

Incomplete Objects Count  =
SUMX (
    SUMMARIZE ( 
       TableName, 
       TableName[Object], 
       "Completion", [Min Completion] ),
    IF ( [Completion] < 1, 1, 0 )
)

How it works: We use SUMMARIZE to group your table by Object. Then, for each object, we find MIN completion (for example, I1234.AAA will have 34). Then, SUMX will loop by the objects, flag with "1" those where completion is < 100%, and then sum up the flags.