By Avichal Singh [Twitter]

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.


Hundreds of products at different list prices…  =>  Grouped based on their List Price Range

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!


Range can be chosen to show 1000s, 100s or 10s based on dataset

Download File
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],
FILTER(Products,
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 :=
CALCULATE (
[Total Sales Amount],
FILTER (
Products,
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 :=
CALCULATE (
[Total Sales Amount],
FILTER (
Products,
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 :=
IF (
ISBLANK ( [Sales Amount by Price Range] ),
0,
[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)