Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Get values from measures of different tables into one

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 !! 

 

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

That was the pain point. It works ! 

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

View solution in original post

4 REPLIES 4
kentyler
Solution Sage
Solution Sage

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"





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Anonymous
Not applicable

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 : 


Capture.PNG

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 : 

Capture_2.PNG

Here is how i set the patrameters for the vizualisation : 

Capture.PNG

 

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

 

Kind regards

Anonymous
Not applicable

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
Not applicable

That was the pain point. It works ! 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors