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

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],
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 :=
IF (
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)

Avi Singh

Avi Singh has personally experienced the transformation and empowerment that Power BI can bring - going from an Excel user to building large scale Power BI solutions. His mission now is to share the knowledge about Power Pivot and Power BI.

This Post Has 12 Comments
  1. Hi Avichal,

    Error in Sales Amount by Price Range Calculation?

    I have been looking at your workbook and notice that the breakdown for Sales Amount by Price Range in the <100 group does not agree with the figures in the lower level.
    Also when you compare the figures for = MIN ( PriceRange[RangeId] )
    && ROUNDDOWN ( Products[ListPrice], 0 )
    <= MAX ( PriceRange[RangeId] )

    Formatting compliments of DAX Formatter

  2. Sales Amount by Price Range:
    [Total Sales Amount],
    FILTER (
    ROUNDDOWN ( Products[ListPrice], 0 )
    >= MIN ( PriceRange[RangeId] )
    && ROUNDDOWN ( Products[ListPrice], 0 )
    <= MAX ( PriceRange[RangeId] )

    Formatting compliments of DAX Formatter

  3. Hello Avichal,
    With the Min of one range equaling the Max of the previous range in the Range Selection Tables, wouldn’t that cause sales of items with a list price equal to those overlapping values to be included in both range summaries? (i.e. a $10 list price would be in the <10 and <20 categories)

    It seems the Max value of each range should be one cent less than the displayed list range (<100, for example).


  4. Sorry for the second post but the overlap could be addressed by either modifying the Max Value or changing the comparison operator from <= to simply = MIN ( PriceRange[RangeId] )
    && Products[ListPrice] = MIN ( PriceRange[RangeId] )
    && Products[ListPrice] < MAX ( PriceRange[RangeId] )

  5. This a very nice solution and I can see it coming in handy. The ‘Sales Amount by Price Range_Show Zeroes’ could be simplified to the following to avoid using the IF:

    Sales Amount by Price Range_Show Zeroes := [Sales Amount by Price Range] + 0

  6. Do you really need the IF(ISBLANK(…. part? Can’t you just multiply the original value by 1, so that blanks turn into zeros?

    1. Konrad,
      I was thinking the same thing. I tried using your *1 approach (I hadn’t seen that one before) on the [Sales Amount by Price Range], but it didn’t end up showing the empty categories.

      However, I had seen a technique of “adding zero” to a formula (sorry, I can’t recall who should get credit for that), and it worked.

      Sales Amount by Price Range :=
      [Total Sales Amount]+0,
      FILTER (
      Products[ListPrice] >= MIN ( PriceRange[RangeId] )
      && Products[ListPrice] <= MAX ( PriceRange[RangeId] )

  7. Ted Murphy has already mentioned that the breakdown for Sales Amount by Price Range in the <100 group does not agree with the figures in the lower level (e.g. for list price < $10 the correct result is $128.468 and not $108.238).

    Can anybody explain why for instance an existing list price of $49.99 is not taken into account (the range 40-50 is blank!) using the non-static price ranges? A static price range 0-10, …, 3990-4000 works fine.

    Certainly, I can use ROUNDDOWN (as shown by Ted) to get correct results but I would love to know why that is necessary.

    1. Meanwhile, I found the mistake in ‘Sales Amount by Price Range’.

      e.g. for the range ‘<50'
      MIN(RangeID) is 40
      but MAX(RangeID) is only 49!
      Thus, an existing list price of $49.99 is not taken into account.

      Anyway, 'Sales Amount by Price Range2' works fine.

  8. PLEASE HELP!!!!!!! First… sorry about my english…..

    I have a hard situation…. I need to make a segmentation with dynamic results all with power pivot

    Table Need:

    Forecast REACH / Skus – Items reach forecast / Sales $$$/ Forecast $$$
    0-49.99% / 15 / 500 / 2,000
    50 – 79% / 12 / 5,000 / 9,000
    80-100% / 7 / 1,500 / 1,600
    101 – 110% / 3 / 3,000 / 2,900

    I already made a disconnected table with Forecast reach range….

    I try many thing, but the classification is dynamic…. not static like a price, is result of a calculation (Reach:=[Sales]/[Forecast])

    The app have to make a calculate & after made the segmentation….. the table isn’t static, because the app is conected by SQL power query…

    Tables Involve

    Fact _Sales
    Disconected_Forecst Range
    Dim-Dates (I have to evaluate every month in year- select in a slicer)
    Dim_Saleperson (I need to see the result by person, slicer)


    Carlos Chavez

    El Salvador, Central America

Leave a Comment or Question