topic Measure totals and subtotals incorrect - tried HASONEFILTER but still not working in DAX Commands and Tips

Measure totals and subtotals incorrect - tried HASONEFILTER but still not working

dancarr22 — Thu, 01 Jul 2021 18:14:37 GMT

Hello,

I have a measure which is grouping fields and then summing. Then taking the average of those summed values.

That works for initial groupings but not for subtotals and grand totals. Need those to calculate average of sums also.

I know there are quite a few explanations of this online - have tried various fixes using HASONEFILTER but haven't worked.

Need average of subtotals/totals at the Firm and MatterType level.

Any help appreciated.

ByMatterTypeAndFirm_Average = AVERAGEX( ADDCOLUMNS( SUMMARIZE( LT,LT[Firm],LT[Matter],LT[Matter Type]), "@Amount", CALCULATE(SUM(LT[Total Amount Billed]))), [@Amount] )

Thanks,

Dan

Re: Measure totals and subtotals incorrect - tried HASONEFILTER but still not working

daxer-almighty — Thu, 01 Jul 2021 20:31:22 GMT

// These helper measures should be hidden. [_HelperMeasure1] = SUM( LT[Total Amount Billed] ) [_HelperMeasure2] = AVERAGEX( DISTINCT( LT[Matter Type] ), [_HelperMeasure1] ) [_HelperMeasure3] = AVERAGEX( DISTINCT( LT[Firm] ), [_HelperMeasure2] ) // This measure will work correctly only // with the hierarchy. ByMatterTypeAndFirm_Average = switch( true(), // You might need to swap the 2nd and 3rd // condition depending on the hierarchy // you have. I've assumed you've got // a hierarchy of Firm > Matter Type > Matter. ISINSCOPE( LT[Matter] ), [_HelperMeasure1], ISINSCOPE( LT[Matter Type] ), [_HelperMeasure1], ISINSCOPE( LT[Firm] ), [_HelperMeasure2], [_HelperMeasure3] )

Re: Measure totals and subtotals incorrect - tried HASONEFILTER but still not working

dancarr22 — Fri, 02 Jul 2021 22:44:31 GMT

Thanks daxer-almighty - that fixed it.
One interesting thing (which I'm not sure about) is for the initial variables which you said to hide - and I did and it worked. Then I tried including them directly in the main formula as VAR(iables) and then referenced those VAR values. But that didn't work.
Anyways, your solution worked! Appreciate your help.

Dan

Re: Measure totals and subtotals incorrect - tried HASONEFILTER but still not working

daxer-almighty — Sat, 03 Jul 2021 15:10:30 GMT

dancarr22

"One interesting thing (which I'm not sure about) is for the initial variables which you said to hide..."

They are not variables, they are measures. A completely different thing.

"Then I tried including them directly in the main formula as VAR(iables) and then referenced those VAR values. But that didn't work."

That did not work because variables in DAX are static. Once calculated, they don't change, so applying any filters to them and/or using them in context transition scenarios do not affect their values.