topic Re: Need help with DAX formula so that grand total will add up in DAX Commands and Tips

Need help with DAX formula so that grand total will add up

Anonymous — Wed, 11 Jan 2023 16:00:09 GMT

I have the formula below for Interim Target and the grand total doesn't add up. I have done a bit of digging and I tried simply chaning the SUMs to SUMX but I get a 'too few arguments' message.

In english the equation is

Any help greatly appreciated

Interim target =

ROUNDUP(

DIVIDE(

(SUM('GS_OE_Target_Data'[Target_Participants]) * (140 - SUM('CRM Data'[selling_days]))),

140

),0)

Re: Need help with DAX formula so that grand total will add up

FreemanZ — Wed, 11 Jan 2023 16:09:44 GMT

https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-Get-Your-Question-Answered-Quickly/m-p/1626726#M32906

Re: Need help with DAX formula so that grand total will add up

Greg_Deckler — Wed, 11 Jan 2023 16:12:25 GMT

Anonymous First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

Re: Need help with DAX formula so that grand total will add up

Anonymous — Wed, 11 Jan 2023 17:04:34 GMT

I've put the PBIX file here.

Interim Target PBIX

Re: Need help with DAX formula so that grand total will add up

Anonymous — Thu, 12 Jan 2023 08:39:21 GMT

Hi Anonymous ,

I think you can try this code to create a measure.

Interim target = VAR _SUMMARIZE = SUMMARIZE ( GS_OE_Target_Data, 'CRM Data'[Code], "Interim target", ROUNDUP ( DIVIDE ( ( SUM ( 'GS_OE_Target_Data'[Target_Participants] ) * ( 140 - SUM ( 'CRM Data'[selling_days] ) ) ), 140 ), 0 ) ) RETURN SUMX ( _SUMMARIZE, [Interim target] )

Result is as below.

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.

Re: Need help with DAX formula so that grand total will add up

Anonymous — Mon, 16 Jan 2023 15:15:22 GMT

That worked Rico - thanks v much