cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PierreL_HOSTIS
Frequent Visitor

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

Accepted Solutions
Highlighted
PierreL_HOSTIS
Frequent Visitor

Re: Get values from measures of different tables into one

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
Super User II
Super User II

Re: Get values from measures of different tables into one

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!




PierreL_HOSTIS
Frequent Visitor

Re: Get values from measures of different tables into one

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

PierreL_HOSTIS
Frequent Visitor

Re: Get values from measures of different tables into one

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

Highlighted
PierreL_HOSTIS
Frequent Visitor

Re: Get values from measures of different tables into one

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors