SUMX not working for variable table

[Robert14358]

Hello All im am having issues with totals in my matrix not summing up my columns properly.

this is my DAX:

AC Hrs (PW) =

VAR SSMAXDATE = [Date Selection (MAX) SS]

VAR MINDATE = [Date Selection (MIN)]

VAR SSPW = [Week Number (PW) SS]

VAR SUMTABLE =

CALCULATETABLE(

    SUMMARIZE(

        Dim_ACT,

        Dim_ACT[ACT_Key],

        "SUM",

        SWITCH(TRUE(),

            ISFILTERED(Date_Selection),

                SWITCH(TRUE(),

                    CALCULATE(SUM(Fact_SNAPSHOT[SS_HRS_AC_Prev_Diff]),FILTER(ALL(Dim_Calendar),Dim_Calendar[Rolling Week #] <= MINDATE-1)) = [AC Hrs],

                    CALCULATE(SUM(Fact_SNAPSHOT[SS_HRS_AC_Prev_Diff]),FILTER(ALL(Dim_Calendar),Dim_Calendar[Rolling Week #] <= SSPW)),

                    CALCULATE(SUM(Fact_SNAPSHOT[SS_HRS_AC_Prev_Diff]),FILTER(ALL(Dim_Calendar),Dim_Calendar[Rolling Week #] <= MINDATE-1))

                ),

            CALCULATE(SUM(Fact_SNAPSHOT[SS_HRS_AC_Prev_Diff]),FILTER(ALL(Dim_Calendar),Dim_Calendar[Rolling Week #] <= SSMAXDATE-1))

        )

    )

)

RETURN

CALCULATE(SUMX(SUMTABLE,[SUM]))

and excel

the individual rows are correct but the totals cant seem to get it right, is there a way to disconnect the totals and sum up the rows disregarding any logic being done on the total?

[Ashish_Mathur]

Hi,

Assuming Act_Key is the first column of your Table visual, see if this measure works

Measure = SUMX(Dim_Act[Act_Key],[AC Hrs (PW)])

[amitchandak]

@Robert14358 , Split into two measures and try

AC Hrs (PW) =

VAR SSMAXDATE = [Date Selection (MAX) SS]
VAR MINDATE = [Date Selection (MIN)]
VAR SSPW = [Week Number (PW) SS]
return
SWITCH(TRUE(),
ISFILTERED(Date_Selection),
SWITCH(TRUE(),
CALCULATE(SUM(Fact_SNAPSHOT[SS_HRS_AC_Prev_Diff]),FILTER(ALL(Dim_Calendar),Dim_Calendar[Rolling Week #] <= MINDATE-1)) = [AC Hrs],
CALCULATE(SUM(Fact_SNAPSHOT[SS_HRS_AC_Prev_Diff]),FILTER(ALL(Dim_Calendar),Dim_Calendar[Rolling Week #] <= SSPW)),
CALCULATE(SUM(Fact_SNAPSHOT[SS_HRS_AC_Prev_Diff]),FILTER(ALL(Dim_Calendar),Dim_Calendar[Rolling Week #] <= MINDATE-1))
),
CALCULATE(SUM(Fact_SNAPSHOT[SS_HRS_AC_Prev_Diff]),FILTER(ALL(Dim_Calendar),Dim_Calendar[Rolling Week #] <= SSMAXDATE-1))
)


AC Hrs (PW) Sum = Sumx(SUMMARIZE(
Dim_ACT,
Dim_ACT[ACT_Key],
"_SUM",[AC Hrs (PW)] ),[_SUM])