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 ...
"DAX " "DAX help" 'Need help' 'DAX'
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.