How to create a measure to MAP QTY range

[ngct1112]

Hi, I am trying to create a measure to map with 2 tables.

How could I SUM the "QTY" from Table 1 (250+120+100+30+310+60 = 870)

Then MAP "870" to Table 2 to find out the "QTY Band" as "501 - 1000" so that to lookup to the
1.) Unit Price as "150"

2.) ID as "8"

Here is my Trial but not really working

Measure = MAXX(Filter(SUMX(Table 1, Table 1[QTY]) > Table 2[From], Table 2[QTY Band])

Appreciate if any help. Here is the Example

 https://drive.google.com/file/d/1Pgs73Q9w6vrUjVHrqChixi1inink0uB2/view?usp=sharing 

Table 1
Product	QTY
1	100
2	50
3	100
4	30
5	50
6	60

Table 2
From	To	QTY Band	Unit Price	ID
1	25	1 - 25	230	1
1	25	1 - 25	250	2
26	100	26 - 100	220	3
101	250	101 - 250	200	4
101	250	101 - 250	210	5
251	500	251 - 500	180	6
251	500	251 - 500	160	7
501	1000	501 - 1000	150	8
1001	5000	1001 - 5000	100	9

[Icey · Accepted solution]

Hi @amitchandak , 

Try this:

MAX Unit Price =
CALCULATE (
    MAX ( 'Table 2'[Unit Price] ),
    FILTER ( 'Table 2', 'Table 2'[QTY Band] = [Lookup QTYBand] )
)

MAX Unit Price ID = 
CALCULATE (
    FIRSTNONBLANK ( 'Table 2'[ID], 1 ),
    FILTER (
        'Table 2',
        'Table 2'[QTY Band] = [Lookup QTYBand]
            && 'Table 2'[Unit Price] = [MAX Unit Price]
    )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@ngct1112 , I think you need Dynamic Segmentation.

See if my video can help:https://youtu.be/CuczXPj0N-k

or these

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.