Banding using the EARLIER function
Previous examples in this tutorial have showed how to use the IF or SWITCH
functions to divide transactions into price bands:
This example will divide transactions into these price bands.
Preparing for the example
The first thing to do is to create a separate table of price bands. One
way to do this is to download this file.
After unzipping it, you can then copy the table the Word document contains:
Click in the table, then click on the cross at the top left to select the entire table. You can then copy it to the clipboard in any of the usual ways.
In SSAS Tabular, go to Diagram view:
Switch to diagram view.
Paste in your table:
One way to paste in the table of data.
Change the table name from Table to PriceBand,
and select OK:
It's very clever how this all works!
Your pasted table will be imported. You shouldn't link this to any other
table:
The imported table of price bands.
The obvious attraction of this approach is that you could create your price
bands in Excel or SQL Server, then update them without changing your model.
Getting the price band for each transaction
Here's how you can show the price band for each transaction:
Showing the price band for each transaction.
The formula for this calculated column is:
= CALCULATE (
VALUES ( PriceBand[PriceBandName] ),
PriceBand[BottomPrice] < EARLIER ( 'Transaction'[Price] ),
PriceBand[TopPrice] >= EARLIER ( 'Transaction'[Price] )
)
This takes a fair bit of explanation, I think! To see how this works,
consider this transaction:
The price for this is £14.85, which is returned (correctly) as
Expensive.
The VALUES
function returns the PriceBandName from the PriceBand
table where the BottomPrice is less than this
transaction's price ...
The first condition states that the bottom price must be less than £14.85, which returns these 3 rows.
... but also where the TopPrice is greater than or equal to
this transaction's price:
The second condition adds a requirement that the top price must be £14.85 or more.
This formula would return an error if the VALUES function returned more than one set of rows at any point,
because a calculated column can't aggregate data or present more than one value.
However, this shouldn't happen (you could always trap for this error with an
IFERROR function to make the formula more robust)
Sorting the price bands
You could now create a pivot table aggregating data by the price bands, but
these wouldn't be sorted correctly (Expensive should be
below Middling):
This pivot table sums the quantity sold by price band.
The obvious solution doesn't work. This would be to display the properties
of the PriceBandName column:
Select this column and press F4 to bring up the properties window.
You could then choose to sort this column by the price band id, which happily
for us would sort the price bands into the correct order:
Choose to sort the price band names by the id column (although it won't work ...).
If you didn't have such a convenient sorting column, you could always create
one by adding a SortOrder column to the PriceBand
table.
Sadly, the solution above doesn't work, because we're not displaying the
PriceBandName column from the PriceBand table,
but rather a calculated column we've concocted ourselves:
The sorting hasn't worked!
The solution is to create another calculated column in the transactions table
which returns the sort order for each transaction:
The final sort order to use.
Here's the formula to use:
=CALCULATE (
VALUES ( PriceBand[PriceBandId] ),
PriceBand[BottomPrice] < EARLIER ( 'Transaction'[Price] ),
PriceBand[TopPrice] >= EARLIER ( 'Transaction'[Price] )
)
What this does is to return the id number of each price band for each
transaction, rather than the name. You can now set this calculated column
to be the sort order column for the PriceBand:
Set the sort order for the derived price band column as above, but this time use the
PriceBandSortOrder that we've calculated.
Yeah!
The price bands are sorted correctly.
Although this is a lot of effort to set up, if you regularly add, delete or edit
thresholds for bands the investment of time up-front will quickly pay off!
