Forum Discussion
How would you approach this? Creating a reference for range of percentages
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 |
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:
2 Replies
mattbriceSolution Sage
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:
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")