Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Lookup a Value and Multiply

Here is my scenario. I am in the telco biz. I have a Discount Plans table, that lists my various discount plan names and their monthly commitments (Discount Plan ID, Discount Plan, Discount Plan Comm...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Anonymous 
    Try filter with Dicount Plan IDs:

     

    Calculated(Discount Plan Shortfall * DiscountRate, Filter(DiscountTable, Region = "EU"), Filter(All(DiscountTable), Discount Plan ID=MAX(Discount Plan ID))

     

    Sample pbix is appreciated for those question related with multiple tables.


    Paul Zheng _ Community Support Team
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Super User
6 years ago

Anonymous , You have  to force row context using summarize or values like

Averagex(summarize(Table,Table[region],Table[Date], "_1",[Discount Plan Shortfall],"_2", [DiscountRate]),[_1]*[_2])
Anonymous's avatar
Anonymous
Not applicable
6 years ago

I don't want an average, I need the exact rate for EU that corresponds to the Discount Plan I use in the Visual Filter. It seems that I could create a measure with Variables, where one Variable would be to Summarize the Discount Plan Rates table by Region and Discount Rate, and then a Variable to filter just EU region. And then a variable to multiply shortfall amount by the filtered discount rate. Just not sure how to write that.