Forum Discussion
Issue with subtotals after multiplication factor
- 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 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] ) ) )
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] )
)
)
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!