topic Dynamic grand total problem depending on switch selection in DAX Commands and Tips

Dynamic grand total problem depending on switch selection

Anonymous — Fri, 10 Mar 2023 19:44:29 GMT

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

Re: Dynamic grand total problem depending on switch selection

amitchandak — Sat, 11 Mar 2023 01:16:16 GMT

Anonymous , Change return like

Sumx( values('MAIN BASELINE'[MAIN BASELINE]), Scenario_value)

Re: Dynamic grand total problem depending on switch selection

Anonymous — Sat, 11 Mar 2023 12:57:49 GMT

HI amitchandak ,

I tried as you indicated but I still don't see the total when I have two scenarios 😞

FEST

Re: Dynamic grand total problem depending on switch selection

tamerj1 — Sat, 11 Mar 2023 13:18:21 GMT

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

Re: Dynamic grand total problem depending on switch selection

Anonymous — Sat, 11 Mar 2023 14:03:34 GMT

Hi tamerj1 ,

It works!!! 🙂

Thank you very much for both resolution suggestions

FEST

topic Dynamic grand total problem depending on switch selection in DAX Commands and Tips

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

I tried to insert a logic for the total but it takes the sum of all the measures without distinction from the selected ones

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

Re: Dynamic grand total problem depending on switch selection

Re: Dynamic grand total problem depending on switch selection

Re: Dynamic grand total problem depending on switch selection

Re: Dynamic grand total problem depending on switch selection

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

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

I am not sure if this is possible, but any help regarding this issue is greatly appreciated! Thank you in advance!
FEST