Issue with subtotals after multiplication factor

[awolf88]

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

[v-chenwuz-msft]

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]

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.

[awolf88]

Hi there!

I appreciate the response and tried wrapping my multiplication into a separate calculate function but without any luck. 

To make it easier to explain, I've rebuilt my problem with fake data and simplified all measures down to the core problem with only a few examples.

The link to the post is here:

Linking tables with multiple value entries - Microsoft Power BI Community

The link to the demo file is here:

https://wetransfer.com/downloads/45264941aaf763a64066883fab1f166420220226082240/7de5ffcd014dc8b3c5bd...

Thanks!

[tamerj1]

HI @awolf88 
I can suggest two options
1. Build many to many relationship (This is the easiest and provides best performance)

m Orders total *factor NEW = 
SUMX (
    VALUES ( Budget[Customer/Prod] ),
    CALCULATE ( 
        SUM ( Sales[Ordered Qty] ) * SUM ( Budget[mult. Factor] )
    )
)

2. Use SUMX - CALCULATE

m Orders total *factor NEW 2 = 
SUMX (
    VALUES ( Budget[Customer/Prod] ),
    CALCULATE ( 
        CALCULATE ( 
            SUM ( Sales[Ordered Qty] ),
            TREATAS (
                VALUES ( Budget[Customer/Prod] ), Sales[Customer/Prod]
            )
        ) * SUM ( Budget[mult. Factor] )
    )
)

You can dowload our file from here https://www.dropbox.com/t/S0LYsMFmymS0PKKC

[awolf88]

Cheers guys, I really liked and appreciate all your solutions!

I've decided to go with @tamerj1 's Version 2 (SUMX-Calculate) although I've also tested @v-chenwuz-msft  and it worked too!

Now I have one more follow-up question I was hoping you could also help me with: My Sales table also comes with a Sales Date which is linked directly via a DimDate table. 

When I filter for one month, say January the numbers are correct. When I filter for a different month, say February, the numbers are correct. If I now try to combine January & February with multi-Selection, my totals go through the roof again. 

What's the logical reasoning behind it with your according measures and is there an easy fix to this? Would be my final piece to the puzzle you guys. 

Your genius is appreciated as always! 🙂

Best,

Alex

[tamerj1 · Accepted solution]

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

[awolf88]

WOW!

You sir are incredible. That totally fixed all of my issues in this complex build of mine. 

Thank you SO SO much for all your input everyone. 

Incredible solutions from all sides!