Forum Discussion

TJI7746's avatar
TJI7746
Regular Visitor
1 year ago

DAX Help with Row totals not adding correctly.

Hi everyone

 

I have the following 2 DAX formulas

 

1) HC Current Quarter =

VAR _maxdate = MAX('Headcount'[Date])

RETURN CALCULATE([Headcount],'Calendar'[Date] = _maxdate)

 

And

 

2) 

HC Previous Quarter =

VAR _maxdate = MAX(Headcount[Date])

VAR _prevq = CALCULATE(MAX(Headcount[Date]),FILTER(ALL('Headcount'),Headcount[Date]<_maxdate))

RETURN CALCULATE([Headcount],'Calendar'[Date] = _prevq)
 
In my table I have added row subtotals from the visualisation menu, but they are not correct. Can someone help me as I am going Crazy.  Sometimes the values show correctly but when i add more filters that's when the subtotals become incorrect.
 

 

 
"DAX " "DAX help" 'Need help' 'DAX'

4 Replies

  • BeaBF's avatar
    BeaBF
    Super User
    1 year ago

    TJI7746 Hi! Try with:

     

    HC Current Quarter =
    VAR _maxdate = MAX('Headcount'[Date])
    RETURN
    IF(
    HASONEVALUE('Calendar'[Date]),
    CALCULATE([Headcount], 'Calendar'[Date] = _maxdate),
    SUMX(
    SUMMARIZE(
    'Headcount',
    'Calendar'[Date],
    "HC", CALCULATE([Headcount], 'Calendar'[Date] = _maxdate)
    ),
    [HC]
    )
    )

     

    HC Previous Quarter =
    VAR _maxdate = MAX('Headcount'[Date])
    VAR _prevq = CALCULATE(
    MAX(Headcount[Date]),
    FILTER(ALL('Headcount'), Headcount[Date] < _maxdate)
    )
    RETURN
    IF(
    HASONEVALUE('Calendar'[Date]),
    CALCULATE([Headcount], 'Calendar'[Date] = _prevq),
    SUMX(
    SUMMARIZE(
    'Headcount',
    'Calendar'[Date],
    "HC", CALCULATE([Headcount], 'Calendar'[Date] = _prevq)
    ),
    [HC]
    )
    )

     

    BBF

    • TJI7746's avatar
      TJI7746
      Regular Visitor
      1 year ago

      This didn't work unfortunately 😞

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi TJI7746 ,

         

        I think you can try SUMX() function to sum the measures to get correct result in subtotal.

        HC Current Quarter (New) = SUMX(SUMMARIZE(Headcount,Headcount[Area],Headcount[SF Legal Gender]),[HC Current Quarter])
        HC Previous Quarter (New) = SUMX(SUMMARIZE(Headcount,Headcount[Area],Headcount[SF Legal Gender]),[HC Previous Quarter])

        If this reply still couldn't help you solve your issue, please share a sample file with us.

         

        Best Regards,
        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • FreemanZ's avatar
    FreemanZ
    Super User
    1 year ago

    hi TJI7746 , 

     

    try like:

    1) HC Current Quarter =
    VAR _maxdate = MAX('Headcount'[Date])
    RETURN 
    SUMX(
        VALUES(headcount[gender]),
        CALCULATE([Headcount],'Calendar'[Date] = _maxdate)
    )
     
    HC Previous Quarter =
    VAR _maxdate = MAX(Headcount[Date])
    VAR _prevq = CALCULATE(MAX(Headcount[Date]),FILTER(ALL('Headcount'),Headcount[Date]<_maxdate))
    RETURN 
    SUMX(
        VALUES(headcount[gender]),
        CALCULATE([Headcount],'Calendar'[Date] = _prevq)
    )