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

[Anonymous]

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)

[Anonymous]

I've put the PBIX file here.  

Interim Target PBIX 

[Anonymous · Accepted solution]

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.

[Anonymous]

That worked Rico - thanks v much

[Greg_Deckler]

@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

[FreemanZ]

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