cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cdawidow
Helper III
Helper III

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!

 

CUSTOMERS.PNG

 

1 REPLY 1
Jihwan_Kim
Super User
Super User

Picture1.png

 

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

 

 

Link to the PBIX file 

 

 

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.


Go to My LinkedIn Page


Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors