Calculating measure across multiple tables

[Anonymous]

Hello,

I have the below model and am looking to set up a measure that will give me a solution the multiplies values for each of the columns highlighted in yellow. Each dimension table contains a numeric conversion from the answers to a survey that is in the 'Commute' table that i would like to give a total value per row

[Anonymous]

Hi @Anonymous ,

Great job!

Best Regards,

Jay

[Anonymous · Accepted solution]

I have managed to generate the correct data as a 'New Column' and the following code. Any advice on a better method would be amazing

CO2 emissions_column = 
VAR dateval =    
    CALCULATE(
        SUM('DateValue'[Number Value - days/week]),
        CROSSFILTER(Commute[Freq], 'DateValue'[Days/Week Value], Both)
    )
VAR Postcode_dist = 
    CALCULATE(
        SUM('Postcode'[Distance (km)]),
        CROSSFILTER(Commute[Postcode], Postcode[Postcode], Both)
    )
VAR Vech_type =
    CALCULATE(
        SUM('Type'[2022]),
        CROSSFILTER(Commute[Commute Index], 'Type'[Index], Both)
    )
VAR EV_data =
        CALCULATE(
        SUM('EV'[Remove_0]),
        CROSSFILTER(Commute[Electric], 'EV'[Electric Vehicle Useage], Both)
    )

VAR result = CALCULATE(
                SUMX('Commute', dateval * Vech_type * Postcode_dist * EV_data * 'Commute'[Car_share_fraction])
)
Return
    result