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!
Our “Morning” Website Traffic is Down 21.5% in Jan 2014 vs. Jan 2013, But “Evening”
Traffic is Up by a Similar Amount, and Full-Day Traffic is “Flat” at +0.9%
(Fake Data, But Real Analysis)
Two Different Flavors of “Time”
Usually, when we talk about “time” in Power Pivot, we’re talking about the Calendar/Date flavor: How much have things changed from yesterday to today. What are our Month to Date numbers, and how do those compare against the same period last year? Let’s call this “macro-trending.”
But time of day is also often interesting: what are the trends WITHIN a day? Let’s call this “micro-trending.”
And then, the hybrid of the two: how are our “micro” trends changing over the course of the year, month, quarter, etc?
I don’t think the techniques here are terribly complicated, but they might be a little difficult to conjure up on your own. So, it’s time for a post – and a downloadable workbook!
The Key: Separate the Date and Time Components!
Let’s say your data comes in looking like this:
Source Data: Each Entry is Stamped with a Single Value of Blended “Date/Time” Type
When we see something like this, alarm bells should go off. We can NEVER work with a “blended” date/time column like this. It’s not going to work, period. Trust me.
So our first step should be to “split” this column into separate columns of “Pure Date” and “Pure Time.”
Using Calculated Columns, We “Split” the original Column into a “Pure Date,”
and a “Pure Hour of Day”
The formulas for those calc columns are:
DATE(YEAR([Date Time]), MONTH([Date Time]), DAY([Date Time]))
A Note on Performance, and Alternatives to Calc Columns
The table of Session data pictured above is a CLASSIC example of a Data Table. Data Tables (aka Fact Tables) are a concept explained in the book and in PowerPivotPro University, but the short version is that Data Tables often get… big. Calculated columns in “big” tables tend to be one of the prime contributors to workbook size and slowness, so I tend to discourage this practice.
It would be much better to perform this split in the original data source, and not even import the “blended” column in the first place. That eliminates the need for calculated columns, AND reduces your overall column count by one (number of columns is ALSO a big contributor to workbook size and slowness).
Similarly, it would be much better to perform the split using Power Query. But if you plan to schedule these workbooks for auto-refresh on a server someday, that might be problematic, because right now, Power Query doesn’t run on any server except the Power BI Cloud servers from Microsoft.
So, the “lowest common denominator” approach is to use calc columns as specified above. If your workbook gets too big or too slow, then try splitting the columns in the original database etc. – OR if you aren’t worried about the Power Query autorefresh limitations, by all means use Power Query to do it.
(These performance implications of calc columns, number of columns, etc. are repeatedly one of TOP the things students and readers gush about with respect to the book and PowerPivotPro University – forgive my shameless plugging here, but it’s the absolute truth. If you lack that knowledge, sooner or later you owe it to yourself to acquire it, regardless of method.)
Next Up: Calendar and “Time of Day” Tables
Now that we have those two columns, we use them to create relationships to Calendar and TimeOfDay tables:
Separate Lookup Tables for Date (Calendar) and Hour (TimeOfDay)
Calendar: One Row Per Day (730 Rows, Since we Have Two Years)
TimeOfDay: One Row Per Hour (24 Rows)
Simple, Straightforward Analysis Time!
Put [Month Year] from Calendar table on the pivot, plus a simple [Session Count] measure:
[Month Year] from Calendar Table. [Session Count] measure from Sessions Table.
where [Session Count] is defined as:
Then we can add [Time of Day] from the Time of Day table:
No Special Math Required, Just Clicked the [Time of Day] Checkbox in the Field List!
Now we can add [Prior Year Session Count] and [Sessions % CHG Since Prior Year]…
Added Measures So We Can Compare to Prior Year – Again, Everything Just Works!
(Sliced to 2014, Since 2013 is our first year of data, so the new measures are blank for 2013)
[Prior Year Session Count]:=
CALCULATE([Session Count], DATEADD(Calendar[Date], -1, Year))
[Sessions % Change vs Prior Year]:=
DIVIDE([Session Count] – [Prior Year Session Count],
[Prior Year Session Count] )
Quick Visual Cleanup
Some conditional formatting, removing some fields, and pivot styling yields:
A More Visually-Polished Result
What if we want something like THIS instead?
Viewing the “Intra-Day” Trend – But Sliceable by Year and Month!
Do to THAT, all we need is a [Sessions per Hour] measure. If we just used [Session Count], we’d skew the results in favor of Late Night, because there are more Late Night hours in the clock (7) than there are Early Afternoon for instance (3).
This leads us to…
[Sessions per Hour]:=
DIVIDE([Session Count], COUNTROWS(TimeofDay))
But that’s NOT the formula I ACTUALLY USED! We want the “Scale” of the chart to be accurate no matter what we select on the slicers. For instance, right now we are looking at a single month – Oct 2014, which has 31 days in it.
So if we clear the slicers, we’ll now be looking at 730 days rather than 31 days, and we don’t want our numbers to inflate 24x.
So we modify [Sessions per Hour] to ALSO divide by number of days:
[Sessions per Hour]:=
COUNTROWS(TimeofDay) * COUNTROWS(Calendar
And the chart scale behaves properly at all times:
Now we are Looking at ALL of 2014, and the Scale Remains in the 1.5 Range
(Yes, I Should Format the Scale and add an Extra Digit)
Did I miss anything you’d like to see? Post a comment