Forum Discussion

awolf88's avatar
awolf88
Icon for Helper II rankHelper II
4 years ago
Solved

Issue with subtotals after multiplication factor

Dearest community, Aware that this is a "common" mistake, I've tried every solution and still can't find an answer to my problem: I have a simple measure written as follows, summing the totals of a...
Help
Help Requested
needhelp
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi awolf88 
    Of course your data model more complex than the sample file. It is not easy to identify the problem without deeply looking into the data. Therefore, the answer to your question is "it depends". It depends on many factors. But I may guess that the month column (Either Month Name or Year Month, whichever you are using) must be involved in table over which SUMX performs its iteration. I believe the following formula would solve the issue

    m Orders total *factor NEW 3 = 
SUMX (
    CROSSJOIN ( VALUES ( Budget[Customer/Prod] ), VALUES ('Date'[Month Name] ) ),
    CALCULATE ( 
        CALCULATE ( 
            SUM ( Sales[Ordered Qty] ),
            TREATAS (
                VALUES ( Budget[Customer/Prod] ), Sales[Customer/Prod]
            )
        ) * SUM ( Budget[mult. Factor] )
    )
)

     

v-chenwuz-msft's avatar
v-chenwuz-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi awolf88 ,

 

Maybe you can try this code to do that if you want sum all the result above with the measure in the total. I create a summarize table to let each row ( include total row) has a progess table to calculate the result.

Measure =
VAR _1 =
    SUMMARIZE (
        'Budget',
        Budget[Customer/Prod],
        [Customer],
        [Product ID],
        [Project],
        [mult. Factor],
        "q*f",
            [mult. Factor]
                * CALCULATE (
                    SUM ( Sales[Ordered Qty] ),
                    FILTER ( Sales, 'Sales'[Customer/Prod] = EARLIER ( Budget[Customer/Prod] ) )
                )
    )
RETURN
    SUMX ( _1, [q*f] )

Result:

 

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.