cancel
Showing results for
Did you mean:
Helper V

## 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
1 ACCEPTED SOLUTION
Community Champion

Hi, @cottrera

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.

Community Champion

Hi, @cottrera

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.

Announcements

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks