Counting Things that Didn’t Happen

 
image

Interesting Question!

Got this question the other day:

I know how to report on the PRESENCE of a certain data element, but can I also report on its ABSENCE ? We’re working with healthcare data and have a list of patients who have had certain diagnostic tests. From this data it’s easy to find patients who have had a procedure (i.e., a mammogram), but can we use the same data to find patients who HAVEN’T had one?

I love it.  Let’s dig in.

Starting Point

Let’s start with a simple Sales data example:

 

image

[Qty Sold] = SUM(Sales[OrderQuantity]

and ProductName comes from a separate Products table:

image

Our First Step

I write a new measure named [Did Not Sell]:

[Did Not Sell] = IF(ISBLANK([Qty Sold]),1,BLANK())

and that yields:

image

Cable Lock, Chain, and Front Brakes Did NOT Sell

And then I can remove the [Qty Sold] measure from the pivot so I just see the ones that didn’t sell:

image

Now We JUST See Things That Didn’t Sell

Where This Fails

That simple “IF ISBLANK” formula might seem too simple, and it is.  Check out the grand total at the bottom of the pivot:

image

The Grand Total Row for this Pivot is Blank

Given our “IF ISBLANK” formula, that is actually “expected” – [Qty Sold] is not blank for that cell, so the IF() evaluates to false, and our [Did Not Sell] measure returns BLANK().

So let’s sum up the blanks instead shall we?

[Products that Did Not Sell] =

SUMX(Products, [Did Not Sell])

That formula iterates over each row in the Products table, summing up the [Did Not Sell] values it gets from each step.

In the case of a single-product row of the pivot, the formula only has one product to evaluate, so we get the same result as [Did Not Sell]:

image

For each single-product row, SUMX returns the same answer.
But for totals, it adds them all up.

So are we finished?  Maybe.

Note 1:  Careful about labels versus rows!

At the top of the pivot we see something disturbing:

image

AWC Logo Cap – Apparently DID Sell But Also DIDN’T?  Didn’t sell TWICE actually – huh?

The answer lies in the Products table itself:

image

There are 3 Rows in the Products Table With That Name
One of those products sold a lot of units, two sold none.

What we DO about this is really up to the needs of the business.  Maybe you leave everything alone, because you WANT to be told that there are two Product ID’s with the name “AWC Logo Cap” which have never sold.

Or maybe you change the formula to explicitly only tell you about entire Names that have never sold:

[Product Names that Did Not Sell] =

SUMX(VALUES(Products[ProductName]), [Did Not Sell])

Which yields:

image

In This Version of the Measure, AWC Logo Cap is NOT Counted as “Unsold”

For more on SUMX, see the Five-Point Palm Exploding Function Technique.

Note 2:  This IS Specific to Products

Since the first input to SUMX in both examples above is “aimed” at the Products table, this will only be useful for counting products.

If you wanted to count, say, Customers who didn’t buy anything, you will need new versions of these measures that are “aimed” at Customers columns/tables.

One Response to Counting Things that Didn’t Happen

  1. hj says:

    Love it, thanks

Leave a Reply