skip to Main Content

power pivot to power bi

A Power BI Technique Mined from the Power Pivot Archives

Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI.  Hard to imagine - even for us - but true.

Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.” 

Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables.  Enjoy! Smile


 
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.

Rob Collie

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *