How to create a measure to MAP QTY range

[ngct1112]

Hi, I would like to modify my question which posted in last week which I still could not figure out how to make it happen after some readings.

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.) MAX 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

Lookup QTYBand = 
var _SUM = SUMX('Table 1','Table 1'[QTY])
var _Group = if(AND( _SUM >= 1 , _SUM <=25 ), "1 - 25"
, if(AND( _SUM >= 26 , _SUM <=100 ), "26 - 100"
, if(AND( _SUM >= 101 , _SUM <=250 ), "101 - 250"
, if(AND( _SUM >= 251 , _SUM <=500 ), "251 - 500"
, if(AND( _SUM >= 501 , _SUM <=1000 ), "501 - 1000", "")))))
return
_Group

[v-janeyg-msft · Accepted solution]

Hello, @ngct1112

It's a pleasure to answer for you.

It's not very clear what kind of visual you really want, I created a measure and I used a card to indicate.

Like this:

Measure =
VAR a =
    SUMX ( 'Table 1', [QTY] )
VAR b =
    SUMMARIZE ( 'Table 2', [QTY Band], "Max", MAX ( 'Table 2'[Unit Price] ) )
VAR c =
    ADDCOLUMNS (
        b,
        "fid",
            CALCULATE (
                MAX ( 'Table 2'[ID] ),
                FILTER (
                    'Table 2',
                    [QTY Band] = EARLIER ( 'Table 2'[QTY Band] )
                        && [Unit Price] = EARLIER ( [Max] )
                )
            )
    )
RETURN
    "total is " & a & "," & " max of unit price is"
        & MAXX ( FILTER ( c, [QTY Band] = [Lookup QTYBand] ), [Max] ) & "," & " id is "
        & MAXX ( FILTER ( c, [QTY Band] = [Lookup QTYBand] ), [fid] )

Here's my pbix file.

https://qiuyunus-my.sharepoint.com/:u:/g/personal/admin_qiuyunus_onmicrosoft_com/EVJ4I-18TRhErRaCwGF...

If you do not solve your problems, please feel free to ask me.

Best regards

Janey Guo

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[lbendlin]

your last version looks reasonable but it can be simplified

Lookup QTYBand = 
var _SUM = SUMX('Table 1','Table 1'[QTY])
var _Group = SWITCH(TRUE()
, _SUM>1000,"1001+"
, _SUM>500,"501 - 1000"
, _SUM>250,"251 - 500"
, _SUM>100,"101 - 250"
, _SUM>25,"26 - 100"
, _SUM>0,"1 - 25"
,"")
return
_Group