Forum Discussion

Zaynah16's avatar
Zaynah16
Icon for Helper I rankHelper I
4 years ago

Filters across multiple tables

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

2 Replies

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.