Forum Discussion
Calculating measure across multiple tables
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
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
2 Replies
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