Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 !!
Solved! Go to Solution.
That was the pain point. It works !
Thank you @kentyler, you mentionned the fact about the measure and not a calculated column.
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"
Help when you know. Ask when you don't!
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
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) !
That was the pain point. It works !
Thank you @kentyler, you mentionned the fact about the measure and not a calculated column.
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |