How would you approach this? Creating a reference for range of percentages

[Anonymous]

I have data which list out the cost / price / margin % of individual items. There is another reference table that list cost range for items to fall in and the targeted gross margin.

SOLUTION NEEDED:

1) To categorize cost of each item by bucketing to a certain cost range

2) Does it meet or exceed the target gross margin by creating an output of either "YES" or "NO".

How would you approach this scenario? I thought about creating IF statements but there are hundreds of items and I am unsure on how to build this out as either a DAX measure or calculated column.

Please see screenshots below.

Your expertise is greatly appreciated!

Table 1 = Sample data with cost / price / margin %

Table 2 = Cost Range / Gross Margin % Target / Falls Within Target Margin?

Cost	Gross Margin	Fall Within Target Margin?
$0.00 - $0.39	83.30%	Yes/No
$0.40 - $0.49	83.30%	Yes/No
$0.50 - $0.99	81.80%	Yes/No
$1.00 - $1.49	80.00%	Yes/No
$1.50 - $3.99	77.78%	Yes/No
$4.00 - $7.99	75.00%	Yes/No
$8.00 - $19.99	71.00%	Yes/No
$20.00 - $39.99	66.67%	Yes/No
$40.00 - $49.99	63.64%	Yes/No
$50.00 - $199.99	60.00%	Yes/No
$200.00 - $1,699.00	55.00%	Yes/No
$1,700.00 - $2,499.00	53.00%	Yes/No
$2,500.00 +	50.00%	Yes/No

[mattbrice · Accepted solution]

You want to assign each Item in Table 1 to a band based on price which is specified in Table 2.   After assigned to a band, compare it to targe Gross Margin % target to determin if it meets goal.  There are a lot of articles out there explaining how to implement banding.  Here is one:

https://exceleratorbi.com.au/banding-in-dax/

[Anonymous]

@mattbrice Thank you so much for your response! I was successful in creating the banding!

I am having a problem with creating the categorization of either "Yes" or "No". Essentially, I am comparing a measure from one table against the Target Gross Margin % of another table (which is a disconnected table). The hope is to generate an output of either "Yes" or "No" from the comparison.

Below is my DAX measure but I am getting an error "A table of multiple values was supplied where a single value was expected". Not sure on how else to go about this because I have tried to rework the syntax a few ways but am still getting the same result.

Any guidance will be greatly appreciated!

Target Gross Margin % =

IF(

    [Margin %_purch] >=

    FILTERS('Cost Gross Margin%'[Gross Margin]),

    "Yes", "No"

)