Forum Discussion

Robert14358's avatar
Robert14358
Resolver III
2 years ago

SUMX not working for variable table

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?

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

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