Lookup a Value and Multiply

[pggolf]

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 Commitment). I have a related table that shows the discounted rates by region for each of the Discount Plans (DiscountPlanRateID, Discount Plan, Region, Discount Plan Rate) . I have a third table that is my Fact Table, which contains current traffic by region by customer. 

I have succesfully created measures to calculate total current traffic for all regions, current traffic costs, proposed monthly commitment, and then any shortfall between current and discount plan. See image.

I need to create a new measure that calculates the Discount Plan Shortfall by the corresponding rate for EU. I used the filter pane to filter the above data by a specific discount plan already. 

So, the formula would be Discount Plan Shortfall * DiscountRate where Region = "EU". I thought it would be easy, but I was getting an error message because the Discount Plan Rates table has multiple rows of EU (for all the different plans) and some have the same rates. Looked at Filter, Relatedtable, etc,. and could not seem to get it to work. 

[V-pazhen-msft · Accepted solution]

@pggolf 
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]

@pggolf , 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])  

[pggolf]

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.