GFITW Strikes Again! Another Power Pivot Training Exercise

Me and Rob were discussing a chapter from our new book: Chapter 25 – Time Intelligence with Custom Calendars: Greatest Formula in the World (GFITW). (This chapter received a complete overhaul by the way. There are many new/updated chapters in the 2nd Ed book).

Chapter 25: Time Intelligence and the Greatest Formula in the World!

I was saying that the GFITW pattern had wide applicability, even outside custom calendars, and was arguing that we should encourage our readers to read that chapter even if their work did not involve a custom calendar (e.g. a 4-5-4 week calendar, see sample or learn more).

Rob stopped me and asked me to provide an example. Put on the spot, I drew a blank. But I didn’t have to wait too long to run into my old friend: the GFITW pattern.

In the spirit of the Beauty Pageant Power BI Training Exercise, here is another one. And hint, the solution involves the GFITW pattern.

For this post, we won’t keep you waiting, and present the problem and the solution together. We do encourage you to take a shot at the problem before you review the solution.

<<DOWNLOAD Problem XLSX File>>

Problem

Note: We have simplified the scenario for the purposes of this training exercise.
Watch the video below or read on for an overview of the problem scenario.

P3 Adaptive ran a crowdfunding campaign, offering multiple perk levels, at which our supporters could contribute. These perks were cumulative as shown in the image below:

The offered perk levels were cumulative

Thus if you ordered the Printed Book (#3), you would also receive DAX sticker (#2) and the e-book (#1). If you ordered the DAX T-shirt you would receive the poster, printed book, sticker and e-book.

Data

Contribution List: The contributor list downloaded from IndieGoGo looks as below:

Our Contribution List Table

Perk: We have also created a simple Excel table listing the perk levels.

Our Perk Table

Goal

Getting a count of all contributions by selected perk is easy. But what we really want is the measure below, which gives us a contribution count for “Selected OR HIGHER Perk”. This way we can determine, for example, how many e-books we truly need to deliver.

The DAX measure that we need to define

<<DOWNLOAD Problem XLSX File>>

Solution

Please take a fair shot at solving the problem yourself before taking a peek at the solution.

<<DOWNLOAD Solution XLSX File>>

Here is the formula using the GFITW pattern (notice the similarity with the image at the top of this post):

ContributionCount_Selected Level:=COUNTROWS(ContributionList)

ContributionCount_Selected Level or HIGHER:=CALCULATE (
[ContributionCount_Selected Level],
FILTER (
ALL ( Perk )
, Perk[Perk#] >= MIN ( Perk[Perk#] )
)
)

Greatest Formula in the World (GFITW)

Once I had set up the tables, my mind immediately went to the GFITW pattern.

Wash, Rinse, Repeat GFITW goes everywhere…even beyond custom calendars

The GFITW pattern is often demonstrated in the custom calendar scenario. The basic workings of the pattern rely on a Numeric ID which identifies the custom calendar period.

GFITW on Custom Calendars requires a numeric PeriodID column

Then GFITW operates by

a) Clearing all filters

b) Then using some navigation arithmetic to select the needed period IDs for the calculation

The specific navigation arithmetic used depends on the measure you are attempting to write – Year-Over-Year, Year-to-Date, Prior Period, Rolling period etc.

In our case, we need to employ a similar strategy not on a custom calendar table but on our Perk table.

Our Perk# is similar to the Numeric ID column used for GFITW

If you would like to learn more about the inner workings and application off the GFITW pattern, you can grab a copy off our e-book today on MrExcel.com.

<<DOWNLOAD Solution XLSX File>>

BTW, I realized I messed up the caption in one of the beauty pageant photos that I posted. To clear that up, here’s my daughter again She’s turning 7 in December.

Power On!
-Avi