Fetch data from RelatedTable and Selected Column

[Anonymous]

I have 4 tables linked as follows:

• Tab_sales and tab_Entity: 1 to many relationship (one side from tab_Entity) using column Entity
• Tab_Entity and Tab_forex: a many-to-many relationship using column CURRENCY
• Tab_forex and Tab_scenario: 1 to many relationship (one side from tab_scenario) using column Scenario
• Tab_scenario_forex: 1 to many relationship (1 side from tab_scenarioForex) using column Scenario

I'd like to create a calculated column in Tab_Entity fetching the value Ex. rate from tab_Forex, changing based on the value selected from tab_scenario, so that I can choose the exchange rate I want to use for Sales (either ACT or Plan).

I tried this way creating a calculated column in tab_Entity:

forex = CALCULATE (SUMX('Tab_forex', 'Tab_forex'[Ex rate]),

RELATEDTABLE(Tab_Forex)

but the results is not filtered by Scenario from tab_Scenario_forex

Anybody can recommend a solution?

Thanks a lot

Mark

Tab_sales

Entity	Sales
USA	100000
Mexico	3000
Italy	2500
France	5000
Germany	2000
France	700
USA	20000
Mexico	5000

Tab_Entity

Entity	Currency
USA	US$
Mexico	MXN
France	EUR
Germany	EUR
Italy	EUR
Spain	EUR

Tab_forex

Currency	Scenario	Ex. rate
EUR	ACT	0.9
EUR	Plan	0.8
US$	ACT	1
US$	Plan	1
MXN	ACT	25
MXN	Plan	24

Tab_Scenario_Forex

Scenario

Plan

ACT

[camargos88]

Hi @Anonymous ,

Please, check if it's what you want: Download PBIX 

Ricardo

[Anonymous]

Hi Ricardo,

many thanks for this.

The problem is still there: even if I select either ACT or Plan from the filter Scenario, the amount of sales doesn't change. The target is to have different amount of sales depending on the selected exchange rate scenario

Mark

[camargos88 · Accepted solution]

@Anonymous ,

I've changed the file. Please check it.  Download PBIX 

I've include the ex.rate in the Sales table.

Ricardo