Forum Discussion

leshugaa19's avatar
leshugaa19
Frequent Visitor
4 years ago
Solved

Lookup value based on a measure

Hello,

I need help to solve this:

I have a measure, let say Sales/ Total Sales and I have a disconnected table with percetages and Amounts and I need to get the amount correponding to the percentage calculated, 

Ex. Ind Sales/ Total Sales = 95.3%

So In this case I should get 2000

 

PercentageAmount
94%1000
95%2000
96%3000

 I tried to use this formula but it's not working:

 

 Var DS=[measure]
Return MAXX(
TOPN (
1,
Table,
ABS (DS) ,ASC
), Table[Amount])
 
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi leshugaa19 ,

    I use the following two tables for testing.
    Table1.

    Table2.

    If you want a calculated column.

    Column = 
VAR _percentage = ROUNDDOWN('Table1'[Percentage],2)
RETURN
LOOKUPVALUE('Table2'[Amount],'Table2'[Percentage],_percentage)

    Or a measure.

    Measure 2 = 
VAR _percentage = ROUNDDOWN([Measure],2)
RETURN
LOOKUPVALUE('Table2'[Amount],'Table2'[Percentage],_percentage)

     

    Attached PBIX file for reference.

     

    Best Regards,
    Community Support Team Gao

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi leshugaa19 ,

    I use the following two tables for testing.
    Table1.

    Table2.

    If you want a calculated column.

    Column = 
VAR _percentage = ROUNDDOWN('Table1'[Percentage],2)
RETURN
LOOKUPVALUE('Table2'[Amount],'Table2'[Percentage],_percentage)

    Or a measure.

    Measure 2 = 
VAR _percentage = ROUNDDOWN([Measure],2)
RETURN
LOOKUPVALUE('Table2'[Amount],'Table2'[Percentage],_percentage)

     

    Attached PBIX file for reference.

     

    Best Regards,
    Community Support Team Gao

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data