Forum Discussion

Tiger2514555's avatar
Tiger2514555
Icon for Helper III rankHelper III
2 years ago

Sub total of the Matrix does not include values.

I'm not sure what's wrong with DAX and how to fix it so that the sub total values ​​can be summed up.

From what I've seen, it only shows sub total if my DAX is followed once before it shows sub total. If there is more than one month it doesn't show sub total.

My DAX:

Week By Week =
VAR FilteredTable =
    FILTER(
        'DemandSummaryAPS',
        'DemandSummaryAPS'[demandID] = SELECTEDVALUE('DemandSummaryAPS'[demandID]) &&
        'DemandSummaryAPS'[item] = SELECTEDVALUE('DemandSummaryAPS'[item]) &&
        'DemandSummaryAPS'[Week Num Due] = SELECTEDVALUE('DemandSummaryAPS'[Week Num Due]) &&
        'DemandSummaryAPS'[Lastest] = 1
    )

VAR MaxWeekNumCreate =

SUMX(
    FILTER(
        FilteredTable,
        'DemandSummaryAPS'[Week Num Create] = MAXX(FilteredTable, 'DemandSummaryAPS'[Week Num Create])
    ),
    'DemandSummaryAPS'[quantity]
)

VAR MinWeekNumCreate = SUMX(
    FILTER(
        FilteredTable,
        'DemandSummaryAPS'[Week Num Create] = MINX(FilteredTable, 'DemandSummaryAPS'[Week Num Create])
    ),
    'DemandSummaryAPS'[quantity]
)
VAR SUM_Week_By_Week = MaxWeekNumCreate - MinWeekNumCreate
RETURN
    MaxWeekNumCreate - MinWeekNumCreate
Need Help

4 Replies

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi Tiger2514555 try v2

    My DAX v2:
     
    Week By Week =
    VAR FilteredTable =
        FILTER(
            'DemandSummaryAPS',
            'DemandSummaryAPS'[demandID] = SELECTEDVALUE('DemandSummaryAPS'[demandID]) &&
            'DemandSummaryAPS'[item] = SELECTEDVALUE('DemandSummaryAPS'[item]) &&
            'DemandSummaryAPS'[Week Num Due] = SELECTEDVALUE('DemandSummaryAPS'[Week Num Due]) &&
            'DemandSummaryAPS'[Lastest] = 1
        )
     
    VAR _max_week_num=
    MAXX(FilteredTable, 'DemandSummaryAPS'[Week Num Create])
     
    VAR _min_week_num=
    MINX(FilteredTable, 'DemandSummaryAPS'[Week Num Create])
     
    VAR MaxWeekNumCreate =
     
    SUMX(
        FILTER(
            FilteredTable,
            'DemandSummaryAPS'[Week Num Create] = _max_week_num
        ),
        'DemandSummaryAPS'[quantity]
    )
     
    VAR MinWeekNumCreate = SUMX(
        FILTER(
            FilteredTable,
            'DemandSummaryAPS'[Week Num Create] = _min_week_num
        ),
        'DemandSummaryAPS'[quantity]
    )
    VAR SUM_Week_By_Week = MaxWeekNumCreate - MinWeekNumCreate
    RETURN
        MaxWeekNumCreate - MinWeekNumCreate
    • Tiger2514555's avatar
      Tiger2514555
      Icon for Helper III rankHelper III
      2 years ago

      The results remain the same, nothing has changed.

      Red is the row where I pressed Total. some_bih 

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi Tiger2514555 without file it is hard to understand details, like 

    'DemandSummaryAPS'[Lastest] = 1

    what this should be? Latest mean...