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

     

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.


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

    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/7de5ffcd014dc8b3c5bdebf48980d49120220226082303/6c3607

     

    Thanks!

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      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's avatar
        awolf88
        Icon for Helper II rankHelper II
        4 years ago

        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