## Prices dependent on Banding

Anything related to PowerPivot and DAX Formuale
major
Posts: 14
Joined: Tue Mar 15, 2016 11:28 pm

### Prices dependent on Banding

hi
This one's been killing me... and I think I just need a nudge in the right direction, or to be told it ain't possible!

I have data model with Sales/Accounts/Products/Bands

There's a column in the Accounts table called Accounts[PotentialUnits] which displays a number from 0-6000. The Bands table is used to segment Accounts based on this potential:
Bands table

BandID / Band / From / To
1 / <1,000 / 0 / 1000
2 / 1,001 - 2,000 / 1000 / 2000
3 / 2,001 - 3,000 / 2000 / 3000
4 / 3,001 - 4,000 / 3000 / 4000
5 / 4,001 - 5,000 / 4000 / 5000
6 / 5,001 - 6,000 / 5000 / 6000

Simple so far. I can do a calculated column using Matt's post https://exceleratorbi.com.au/banding-in-dax/

But this is where it gets tricky, for me:

- the products table has 3 products and accounts buy all, some or non of these products
- the price of the products depends on the band the account is in
- the price could be in another table or merged into the products table as a column for each band; wherever works

So prices would be as follows (or transposed if better):

Product / Band 1 / Band 2 / Band 3 / etc.....
A / 20 / 10 / 5
B / 40 / 20 / 10
C / 60 / 40 / 20

What I want to show in a pivot table is:

Band / Account Name / Product / Units Purchased / Correct Price (based on the Banding the account falls into)
<1000 / Steve / A / 12 / 20
<1000 / Steve / B / 50 / 40
<1000 / Steve / C / 17 / 60
<1000 / Bob / A / 12 / 20
1001 - 2000 / Mike / A / 2 / 10 (mike has greater potential so gets the lower price for product A)

I have got as far as the first 4 columns, but stuck on how to get those band prices in and how to filter them according to the accounts banding

Hope this makes sense!