DAX Calculating distributed spend using a distribution table

[cottrera]

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

[Jihwan_Kim · Accepted solution]

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.

Linkedin: https://www.linkedin.com/in/jihwankim1975/