Forum Discussion
Anonymous
3 years agoNot applicable
Dynamic grand total problem depending on switch selection
Hello community,
I'm unsuccessfully trying to show the dynamic Grand Total as the sum of the columns depending on whether or not I select the "Scenario" filter to show more measures.
If I select one measure the total appears
but if I select two or more selections it does not appear
The reason is that the measures do not talk to each other using the "Scenario" selector which is detached from the model and is used within the measures via the SWITCH function.
I insert the measure, for simplicity I remove some scenarios
Sum_Scenario_Year =
//example of recalled measures
//ACT CY = CALCULATE(SUM('CAPEX FOR ENG'[SCENARIO_VALUE]),'CAPEX FOR ENG'[SCENARIO] = "ACT" , DATESYTD(TIME[Date]))
// ACT - 1 = CALCULATE(SUM('CAPEX FOR ENG'[SCENARIO_VALUE]),'CAPEX FOR ENG'[SCENARIO] = "ACT" , DATEADD(DATESYTD(TIME[Date]), -1, YEAR))
// PCL CY = CALCULATE(SUM('CAPEX FOR ENG'[SCENARIO_VALUE]),'CAPEX FOR ENG'[SCENARIO] = "PCL" , DATESYTD(TIME[Date]))
VAR Scenario_value =SWITCH(SELECTEDVALUE('MAIN BASELINE'[MAIN BASELINE]),
"ACT CY" , [ACT CY],
"ACT -1", [ACT -1],
"PCL CY" , [PCL CY],
)
RETURN
Scenario_value
I tried to insert a logic for the total but it takes the sum of all the measures without distinction from the selected ones
//if (hasonevalue('MAIN BASELINE'[MAIN BASELINE]),Scenario_value , [ACT CY]+[ACT -1]+[ACT -2]+[ACT -3]+[FCT CY]+[BDG CY]+[PCL CY]+[EST CY]+[CMT CY]+[FTR + 1]+[FTR + 2]+[FTR + 3]+[FTR + 4]+[FTR + 5])The model involved is this one
I am not sure if this is possible, but any help regarding this issue is greatly appreciated! Thank you in advance!
FEST
Hi Anonymous
please try
Sum_Scenario_Year =
SUMX (
VALUES ( 'MAIN BASELINE'[MAIN BASELINE] ),
SWITCH (
'MAIN BASELINE'[MAIN BASELINE],
"ACT CY", [ACT CY],
"ACT -1", [ACT -1],
"PCL CY", [PCL CY]
)
)
4 Replies
- amitchandakSuper User
Anonymous , Change return like
Sumx( values('MAIN BASELINE'[MAIN BASELINE]), Scenario_value)
- AnonymousNot applicable
HI amitchandak ,
I tried as you indicated but I still don't see the total when I have two scenarios 😞
FEST
- tamerj1Community Champion
Hi Anonymous
please try
Sum_Scenario_Year =
SUMX (
VALUES ( 'MAIN BASELINE'[MAIN BASELINE] ),
SWITCH (
'MAIN BASELINE'[MAIN BASELINE],
"ACT CY", [ACT CY],
"ACT -1", [ACT -1],
"PCL CY", [PCL CY]
)
)- AnonymousNot applicable