Forum Discussion
Helper IIIssue with subtotals after multiplication factor
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 issuem 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] ) ) )
Helper IIHi 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:
Thanks!
Community ChampionHI 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
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 issuem 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!