Forum Discussion
Looping a measure in DAX Using SUMX
Hi guys, I am having trouble with my sumx calculation to model the new sales amount based on modifying a customers discount for specific products ( propsoed discount) I have included an illustration to explain very simply my desired output and the logic behind the calculation .
Essentially, I want to loop through each customers individual product purchases and use their proposed discount to model their new sales amount but also get the total of the new sales amount across all our customers.
I have been using SUMX(VALUES(CUSTOMER), (LIST PRICE *(1-PROPOSED DISCOUNT)*AVERAGE QUANTITY SOLD )
but the total value ( ie when factoring the entire customer base) is always incorrect.
Any help would be appreciated!
1 Reply
- Jihwan_Kim
Super User
Sales Total : =
VAR _customerlist =
VALUES ( Customers[Customer] )
VAR _salestotal =
ADDCOLUMNS (
_customerlist,
"@salestotal",
CALCULATE (
SUMX ( RELATEDTABLE ( Sales ), Sales[QS] * RELATED ( Products[Price] ) )
* ( 1 - SELECTEDVALUE ( Customers[Discount] ) )
)
)
RETURN
SUMX ( _salestotal, [@salestotal] )