Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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] )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |