cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cottrera
Helper V
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

AddressSpend
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
    
DistributionMinMaxOrder
£0-£50005001
£500-£2,00050120002
£2,000-£3,000200130003
£3,000-£4,000300140004
£4,000-£20,0004001200005
£20,000-£50,00020001500006

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
AddressSpendExpected 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
Jihwan_Kim
Community Champion
Community Champion

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/

 
 

View solution in original post

1 REPLY 1
Jihwan_Kim
Community Champion
Community Champion

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/

 
 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors