Forum Discussion
Solved
DAX Calculating distributed spend using a distribution table
Hi
I have an address table with a dax function that give me spend per address
| Address | Spend |
| 1 Nonsense Street, London | £200 |
| 2 Unknown Road, London | £1,506 |
| 3 High Road, London | £20,000 |
| 4 Dax Lane, London | £3,200 |
| 5 Power Road, London | £760 |
| 6 Excel Street, London | £901 |
| 7 Variable Lane, London | £40,000 |
| 8 Calculate Street, London | £2,400
|
I have a second table I would like to use for calcualting distribution of spend by address
| Table called *Repairs Spend Distribution | |||
| Distribution | Min | Max | Order |
| £0-£500 | 0 | 500 | 1 |
| £500-£2,000 | 501 | 2000 | 2 |
| £2,000-£3,000 | 2001 | 3000 | 3 |
| £3,000-£4,000 | 3001 | 4000 | 4 |
| £4,000-£20,000 | 4001 | 20000 | 5 |
| £20,000-£50,000 | 20001 | 50000 | 6 |
Dax I have tried but not working
Repairs Spend Distibution =
CALCULATE(
SELECTEDVALUE('*Repairs Spend Distribution'[Repairs Spend],BLANK()), FILTER(
ALL('*Repairs Spend Distribution'), [Repairs Spend]
>= '*Repairs Spend Distribution'[Min] && [Repairs Spend]
<= '*Repairs Spend Distribution'[Max]))
I get an error when adding this DAX to my visual results. I was expecting this result
| Address | Spend | Expected result |
| 1 Nonsense Street, London | £200 | £0-£500 |
| 2 Unknown Road, London | £1,506 | £500-£2,000 |
| 3 High Road, London | £20,000 | £4,000-£20,000 |
| 4 Dax Lane, London | £3,200 | £3,000-£4,000 |
| 5 Power Road, London | £760 | £500-£2,000 |
| 6 Excel Street, London | £901 | £500-£2,000 |
| 7 Variable Lane, London | £40,000 | £20,000-£50,000 |
| 8 Calculate Street, London | £2,400 | £2,000-£3,000 |
Can you advise where I am going wrong.
Richard
Hi, cottrera
Please try the below.
Repairs Spend Distibution =
CALCULATE (
SELECTEDVALUE ( '*Repairs Spend Distribution'[Distribution], BLANK () ),
FILTER (
ALL ( '*Repairs Spend Distribution' ),
[Repairs Spend] >= '*Repairs Spend Distribution'[Min]
&& [Repairs Spend] <= '*Repairs Spend Distribution'[Max]
)
)Also, please check [Repairs Spend] is a measure, not a column.Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
1 Reply
Hi, cottrera
Please try the below.
Repairs Spend Distibution =
CALCULATE (
SELECTEDVALUE ( '*Repairs Spend Distribution'[Distribution], BLANK () ),
FILTER (
ALL ( '*Repairs Spend Distribution' ),
[Repairs Spend] >= '*Repairs Spend Distribution'[Min]
&& [Repairs Spend] <= '*Repairs Spend Distribution'[Max]
)
)Also, please check [Repairs Spend] is a measure, not a column.Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.