Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

  • Anonymous , Change return like

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

  • Anonymous's avatar
    Anonymous
    Not applicable

    HI amitchandak , 

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

     

    FEST

  • tamerj1's avatar
    tamerj1
    Community 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]
    )
    )

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi tamerj1 , 

      It works!!! 🙂

      Thank you very much for both resolution suggestions

      FEST