DAX Help with Row totals not adding correctly.

[TJI7746]

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.

[FreemanZ]

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)

)

[BeaBF]

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

This didn't work unfortunately 😞

[Anonymous]

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.