Filters across multiple tables

[Zaynah16]

Hi guys, 

Please help

I am trying to apply a filter across multiple tables. 

I have calculated Return on Investment as below however revenue, cost Savings and cost incurred are from the annual Returns table and spend to date is from another table and therefore reflecting incorrecly. Please help

calculation

Revenue+Cost_savings-Cost_incurred/SpendToDate*100

DAX

ROI =

VAR _Revenue = CALCULATE(SUM('Annual Returns (2)'[Value]),FILTER('Annual Returns (2)','Annual Returns (2)'[Measure]="Revenue"))

VAR _Cost_savings = CALCULATE(SUM('Annual Returns (2)'[Value]),FILTER('Annual Returns (2)','Annual Returns (2)'[Measure]="Cost savings"))

VAR _Cost_incurred = CALCULATE(SUM('Annual Returns (2)'[Value]),FILTER('Annual Returns (2)','Annual Returns (2)'[Measure]="Cost incurred"))

VAR _SpendToDate = CALCULATE(SUM('SDG Goals'[Spend To Date]))

VAR _ROI = ((_Revenue+_Cost_savings-_Cost_incurred)/_SpendToDate)*100

RETURN

_ROI

Kind Regards 

Zaynah

[v-angzheng-msft]

Hi, @Zaynah16 

Could you please consdier sharing more details about it and posting expected result so it is clear on what needs to be implemented?  And It would be great if there is a sample file without any sesentive information here.

It makes it easier to give you a solution.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Zaynah16 , n such cases you need have common dimensions like date , item etc and analyze data against them

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/