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 column with 2 treatas functions combined:

What I'm trying to do after is multiplying it's values by a different column, a factor column that =1 or is smaller than 1 values. My approad therefore looked like this:

And the problematic result I somehow receive looks like the following and I cannot wrap my head around why:

Column 1 ("fakturiert clean") shows my first measure. A simple total with subtotals that all add up. But as Soon as I try to add my multiplication as in column 2 ("fakturiert"), then my Subtotals and Grand total go through the roof. Tried everything from adding separate "Calculate" functions, but can't seem to get it it work. 

 

Would appreciate any kind of input from you geniuses out there as always!

 

Thanks in advance!

Alex

 

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

     

7 Replies

  • 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.

     

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    encapsulating the second sum in  a CALCULATE should normally do it.  Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Avoid posting screenshots of your source data if possible.

    Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.