Lookup tables and calculate % value.

[komaragiri]

I have 2 tables.  

Vendor Name	Total Value
ABC	10000
XYZ	20000
ABCD	50000

T2-Vendor	From Value	To Value	Percentage
ABC	0	5000	5%
ABC	5001	10000	10%
ABC	10001	100000	20%
XYZ	0	5000	5%
XYZ	5001	10000	10%
XYZ	10001	100000	20%
ABCD	0	100000	15%

I need to Look Vendor name from Table 1 to Table 2.  Once I find the value, See if the Total Value from Table 1 falls in to which range to get the Percentage Value.   Store the Pecentage value on the Table 1 as column against each row.  

Need DAX formula for column creation. 

Final Results (Table 1, after adding colum pecentage)  

Vendor Name	Total Value	Percentage	Discount
ABC	10000	10%	1000
XYZ	20000	20%	4000
ABCD	50000	15%	7500

Appreciate your help.  

[some_bih · Accepted solution]

Hi @komaragiri create two columns in vendor table as

Value Percentage and Discount. Adjust your table names / columns accordingly and you should get Output

Value Percentage =

CALCULATE(

    MAX(Table2[Percentage]),

        FILTER(

        Table2,Table2[Vendor]=Vendor[Vendor Name] && Table2[From Value]<=Vendor[Total Value] && Table2[To Value]>=Vendor[Total Value]

        )

)

Discount = ROUND(Vendor[Total Value]*Vendor[Value Percentage],2)

Output

[komaragiri]

thank you for your solutions 

[AntrikshSharma · Accepted solution]

@komaragiri You can build a relationship between 2 tables assuming the Table1 won't have duplicates in future.

In Table1 you can create a the column using:

Discount = 
VAR T1_TotalValue = Table1[Total Value]
VAR T2_SameRows = 
    CALCULATETABLE ( 
        Table2,     
        T1_TotalValue >= Table2[From Value]
            && T1_TotalValue <= Table2[To Value] 
    )
VAR Result = 
    SUMX ( T2_SameRows, Table2[Percentage] * T1_TotalValue )
RETURN 
    Result

You can also create the column in Table2 using:

Discount = 
VAR T1_TotalValue = 
    RELATED ( Table1[Total Value] )
VAR ValueInRange = 
    T1_TotalValue >= Table2[From Value]
        && T1_TotalValue <= Table2[To Value]
VAR Result = 
    ( Table2[Percentage] * T1_TotalValue ) * ( ValueInRange * 1 )
RETURN 
    Result

[some_bih · Accepted solution]

Hi @komaragiri create two columns in vendor table as

Value Percentage and Discount. Adjust your table names / columns accordingly and you should get Output

Value Percentage =

CALCULATE(

    MAX(Table2[Percentage]),

        FILTER(

        Table2,Table2[Vendor]=Vendor[Vendor Name] && Table2[From Value]<=Vendor[Total Value] && Table2[To Value]>=Vendor[Total Value]

        )

)

Discount = ROUND(Vendor[Total Value]*Vendor[Value Percentage],2)

Output