Get values from measures of different tables into one

[Anonymous]

Hello Everyone,

It's always a pleasure to ask a question to the community and I can't achieve to solve my issue, so i thought someone could help me out. 

Here is my issue : 

I have 7 different tables that have relationship with a referential table (so it's a total of 8 tables).

For each of these 7 tables I build a measure that is dedicated to the table which shape is :

TrimMixValue:= 

var ref_cycle = SELECTEDVALUE(Referenced_cycle[Cycle])
var ref_period = ALLSELECTED(Referenced_cycle[Month_Year])

var obs_cycle = SELECTEDVALUE(Observed_cycle[Cycle])
var obs_period = ALLSELECTED(Observed_cycle[Month_Year])

var co2_ref = CALCULATE(SUM(TrimMix[AMOUNT_CO2]);FILTER(TrimMix; TrimMix[CO2_RESULT_Cycle] = ref_cycle && TrimMix[CO2_RESULT_Month_Year] in ref_period))
var vol_ref = CALCULATE(SUM(TrimMix[Vol_sales]);FILTER(TrimMix; TrimMix[CO2_RESULT_Cycle] = ref_cycle && TrimMix[CO2_RESULT_Month_Year] in ref_period))

var avg_ref = DIVIDE(co2_ref ; vol_ref)
var vol_obs =CALCULATE(SUM(TrimMix[Vol_sales]);FILTER(TrimMix; TrimMix[CO2_RESULT_Cycle] =obs_cycle && TrimMix[CO2_RESULT_Month_Year] in obs_period))

var table_obs = SELECTCOLUMNS(
                FILTER(TrimMix; TrimMix[CO2_RESULT_Cycle] = obs_cycle && TrimMix[CO2_RESULT_Month_Year] in obs_period )
                ;"Trim_level";TrimMix[LCDV14_Code_Programming_GLP_Trim_level]; "MT_CO2_OBS1"; TrimMix[AMOUNT_CO2];"Volume_obs1";TrimMix[Vol_sales])
				
VAR table_obs_2 =GROUPBY (  
table_obs;   
[Trim_level];     
"MT_CO2_OBS_test"; SUMX( CURRENTGROUP(); [MT_CO2_OBS1]);
"Volume_obs_test"; SUMX( CURRENTGROUP(); [Volume_obs1]))

VAR table_obs_3 =  ADDCOLUMNS(table_obs_2; "AVG_CO2_OBS"; DIVIDE([MT_CO2_OBS_test];[Volume_obs_test]);"Mix_obs";DIVIDE([Volume_obs_test];SUMX(table_obs_2;[Volume_obs_test])))


var table_ref = SELECTCOLUMNS(
                FILTER(TrimMix; TrimMix[CO2_RESULT_Cycle] = ref_cycle && TrimMix[CO2_RESULT_Month_Year] in ref_period )
                ;"Trim_level";TrimMix[LCDV14_Code_Programming_GLP_Trim_level]; "MT_CO2_REF1"; TrimMix[AMOUNT_CO2];"Volume_ref1";TrimMix[Vol_sales])
				
VAR table_ref_2 =GROUPBY (  
table_ref;   
[Trim_level];     
"MT_CO2_REF_test"; SUMX( CURRENTGROUP(); [MT_CO2_REF1]);
"Volume_ref_test"; SUMX( CURRENTGROUP(); [Volume_ref1]))

VAR table_ref_3 = ADDCOLUMNS(table_ref_2; "AVG_CO2_REF"; DIVIDE([MT_CO2_REF_test];[Volume_ref_test]);"Mix_ref";DIVIDE([Volume_ref_test];SUMX(table_ref_2;[Volume_ref_test])))

VAR Result =
    NATURALLEFTOUTERJOIN ( table_ref_3;table_obs_3)

RETURN DIVIDE(SUMX(Result;([AVG_CO2_REF] - avg_ref)*[Volume_obs_test]*([Mix_obs] - [Mix_ref]));vol_obs)

What I would like to do now is to get those 7 measures values from my table to a final table that i'm going to use for a waterfall chart. 

I don't know how can I manage this part into Power BI Dax syntax and it needs to be done with Dax syntax (No power Query)...

Please, can someone help me out with this ? 

Thank you in advance for your help !! 

[kentyler]

Measures are not really part of tables, although you create them that way in the interface. Create your 7 Measures and name with an index --  TrimMixValue_1 = ..... TrimMixValue_2 = ....

Then you sould be able to place all 7 measures on a report together with your "final table"

[Anonymous]

Hello Kentyler, 

I tried to add it such as this exemple : https://community.powerbi.com/t5/Desktop/Waterfall-Chart-using-multiple-measures/td-p/439275/page/2

So I created a table like that : 

Of course the Var column correspond to the name of my measure, the screenshot below is an exemple. 

And then, when I created this table, I created a function to create a calculated column such like below : 

=SWITCH(SELECTEDVALUE(SUMMARY_Table[Index]);
		1;+[Measure 1];
		2;+[Measure 2];
		3;+[Measure 3];
		4;+[Measure 4];
		5;+[Measure 5];
		6;+[Measure 6];
		7;+[Measure 7];
		8;+[Measure 8];
		9;+[Measure 9];
		BLANK())

And then, when I tried to display the values of my measures, I can't vizualise anything, either the index or measure name : 

Here is how i set the patrameters for the vizualisation : 

Do you think that follows your answer and how can I finally get my measures visualised into this chart ? 

Kind regards

[Anonymous]

Okay, got it. 

I think that is not a calculated column that I had to add but a new measure. 

I'll give it a shot and let you know if it works or not (let's crossfingers) ! 

[Anonymous · Accepted solution]

That was the pain point. It works ! 

Thank you @kentyler, you mentionned the fact about the measure and not a calculated column.