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!
Dynamic segmentation or banding has been covered in PowerPivotPro articles in the past and in beautiful detail by the Italians – Marco Russo, Alberto Ferrari (these folks are literally “off the charts!” in Matt’s representation of Power Pivot skill levels 🙂 ).
It involves grouping your data in specific numeric bands or segments; for example looking at your sales data based on the price range of your products. You have a long list of price points for your products, instead of looking at each price point individually it would be nice to group them into segments say to represent the low, medium and high price items in your catalog.
Variable Range Selection
That is great, however it is hard to predefine segments that would work well in all scenarios. As your data changes over time, or as users slice and dice your existing data (e.g. filter to a specific region, product category or year) the segments may prove to be either too granular or not granular enough. In the case below, the predefined range does not have enough grain or detail and pretty much everything ends in one bucket ($3000-$4000).
Predefined Segment Ranges may prove too granular or not granular enough
as you work your data
What if your segments had a range of options from high to low granularity, so that you could choose the right segments based on the data and your need!
Watch on YouTube or keep reading…
Revisit Dynamic Segmentation
Let’s first quickly review dynamic segmentation using static predefined ranges. The way this works is using a disconnected table to define the segment ranges and then using them in a measure that calculates your numerical value.
Static Price Range Table
Sales Amount by Static Price Range:=CALCULATE([Total Sales Amount],
Products[ListPrice]>=MIN(StaticPriceRange[Min Price]) && Products[ListPrice]<=MAX(StaticPriceRange[Max Price])
This would generate output similar to shown above.
However, once defined (at whichever grain you chose 1000s, 100s, 10s or something else) – you do not have the option to chose a different grain if needed. So let’s see how we can define segments of variable grain, so we can use the grain that makes best sense.
Variable Range Selection
The trick is somewhat simple. You construct your segment range table slightly differently, as shown below, be defining multiple segment ranges of variable grain.
New Price Range Table with Selector Columns of Variable Grains
And define your measure slightly differently
Sales Amount by Price Range :=
[Total Sales Amount],
Products[ListPrice] >= MIN ( PriceRange[RangeId] )
&& Products[ListPrice] <= MAX ( PriceRange[RangeId] )
That’s it! Now you can choose the segment range that makes best sense with the data that you are viewing.
Building Range Selection Tables
To keep things simple, we chose each row of our price range table to be $1 (indicated by RangeID column in image above). Thus we have 4000 rows to represent the range from 0 to 4000. That’s probably too many already; imagine if our range needed to be from 0 to say 1,000,000! Would we need a million tows.
Turns out, you do not need a row for each unit. You only need to define the range table, based on the lowest granular range that you want to see.
For example, if 10s is the lowest grain we want, we can define our range table as below. You can see this one only has 400 rows to cover numerical range of 0 to 4000 (10 x 400 = 4000). If you would choose 100s to be your lowest grain, you would only need 40 rows.
New Range Selector Table fits in only 400 rows
governed by the lowest chosen grain
The measure gets slightly redefined based on our new range table
Sales Amount by Price Range2 :=
[Total Sales Amount],
Products[ListPrice] >= MIN ( PriceRange2[Min Value] )
&& Products[ListPrice] <= MAX ( PriceRange2[Max Value] )
Improving Graphical Display
As is typical rows where the measure returns blank are not shown in pivot or graph. While this may work for pivot, it looks downright weird on a graph – see image below.
Axis looks weird with no continuity
To address this we define a new incremental measure (based on our previous measure – you are following the best practice #4 aren’t you?)
Sales Amount by Price Range_Show Zeroes :=
ISBLANK ( [Sales Amount by Price Range] ),
[Sales Amount by Price Range] )
And the graph looks much prettier now with a continuous axis. This is much less jarring to users as they drill down, up and across in the data set.
Continuous Axis makes a lot more sense
Power on my friend!
Download File (Excel 2013)