I had a requirement from the customer where I am consulting and what they wanted was to be able to have dynamic banding. So that no matter what they put into the table it would show the banding where it would not go greater than the banding amount.

This is easiest shown with an image, so if the band was < $1000 the amounts should not be greater than $1000.

So as you can see above in this context the Banding is working as expected, showing values that are < $1000

But when you remove the Document No. I got the following shown below.

Now whilst the SUM Amount is correct, the banding is incorrect, because the SUM Amount is greater than $1000.

So below I show how I got this working.

Creating my Value Banding Disconnected Table

The first thing that I had to do was to create a disconnected table which would hold my banding.

  • To do this I used the Enter Data from the Ribbon in Power BI Desktop
  • And I put in the following details
  • I then clicked Ok which loaded it into the Data Model.

Creating measures for my Bands

Next I had to create individual measures for each of the above bands.

  • I found that this was the easiest way to ensure that when the data was being filtered by which ever field the user used, it would bring back the correct results.
  • I then created my 5 measures as shown below, the reason for using the IF statement is so that if the measure is not within the banding bounds make it BLANK. Which effectively in Power BI will not show any data.
    Band 0 - 100 =
IF ( [Amount] >= 0 && [Amount] < 100, [Amount], BLANK () )

Band 100 - 1000 =
IF([Amount] >= 100 && [Amount] < 1000, [Amount], BLANK() )

Band 1000 - 10000 =
IF([Amount] >= 1000 && [Amount] < 10000, [Amount], BLANK() )

Band 10000 - 100000 =
IF([Amount] >= 10000 && [Amount] < 100000, [Amount], BLANK() )

Band > 100000 =
IF([Amount] > 100000 , [Amount], BLANK() )

                            

Final Measure to be used in Table or Visuals

This is the final measure which I can use in a table or visuals. And this uses a combination of the disconnected table and the measures we created above.

  • Here is the measure.
    Amount Bands =
IF (
  HASONEVALUE ( 'Value Banding'[Band] ),
  SWITCH (
    VALUES ( 'Value Banding'[Band] ),
    "< $100", [Band 0 - 100],
    "< $1 000", [Band 100 - 1000],
    "< $10 000", [Band 1000 - 10000],
    "< $100 000", [Band 10000 - 100000],
    "$100 000 +", [Band > 100000]
  ),
  [Band > 100000]
)

                    

  • What the above measure is doing, is looking to see which item has been selected on the Disconnect Table and display the corresponding measure.
  • As well as if nothing is selected to default to > $100 000

What it looks like in Action

Below is now what it looks like when I select < $100 and have it using the Document No. (This table is sorted highest to lowest)

And then again if I change it to < $100 000 I see the following for the Document No. (This table is sorted highest to lowest)

Then I did a test where I dragged in a field that I know would aggregate to a higher level, and I wanted to ensure that it would display correctly. And as you can see below, it has put the correct Reason into the correct Bands.

Conclusion

So by creating the dynamic banding I was able to create something for the customer to get some really valuable insights into their data.