cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

DAX calculation of variable inside iterator SUMX

Hi guys! 

Really need your assistance.

I've been struggling with one case for more than week...

 

Input: 

Table 'F_ERS_SellOut' with sales transactions. 

Each row is fact of weekly sales [PostingDate] for one product [ProductCode] in specified store [StoreCode] .

forum.PNG

 

My task:

I need to calculate [sales amount] of products with the price 20% lower than their regular price

I've stuck with regular price definition... Regular price is the max price [UnitPrice] of product in particular store for the last 70 days from "current" date [PostingDate]

 

My solution and thoughts (below attached code script too):

forum2.PNG

 

The measure always returns blank.

I realize that my mistake somewhere in calculation of Regular_price variable.

And also know that I have poor understanding of context transition inside nested iteartors so far...

I will be gratefully thankfull of brief explanation of what I'm doing wrong..

 

Thank everybody in advance!

 

Value_of_discounted_products = 
            SUMX(                     
                FILTER(F_ERS_SellOut;     
                          VAR Cur_Date = F_ERS_SellOut[PostingDate] 
                          VAR Regular_price =    
                                             MAXX (                    
                                                CALCULATETABLE (       
                                                ALL(F_ERS_SellOut[PostingDate]);
                                                DATESINPERIOD ( 'Calendar'[Date]; Cur_Date; -70;DAY) 
                                                                );                                                       
                                               F_ERS_SellOut[UnitPrice]
                                                        )
                            RETURN
                            F_ERS_SellOut[UnitPrice] <= (Regular_price*0,8)); 
                            [Value])
1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@Anonymous ,

 

You may take a look at the post below.

https://community.powerbi.com/t5/Desktop/Adjusting-Averages-by-a-fixed-percentage/m-p/592427#M281122

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
ed_mcdill
Advocate I
Advocate I

Hi @Anonymous I'm dancing around the same problem, and saw that you listed this problem as solved. Was it due to declaring the variable inside the Filter? I was looking at one of Alberto's first posts on variables https://www.sqlbi.com/articles/variables-in-dax/ and he talks about this. I don't suppose you would be able to share your measure if you were able to solve it?

 

Ed 

Anonymous
Not applicable

Hi @ed_mcdill ,

Honestly, I don't remember what exact steps I applyied in order to cope with question at that time.

As knowledge source I was inspired with this article too:

https://www.sqlbi.com/articles/optimizing-nested-iterators-in-dax/

Nowadays I include additional transformation step in my ETL process to calculate nested logic on MS SQL Server side.

In Power BI I send alredy precalculated column, so it doesn't need to compose something complex in my case.

 

BR,

Egor

v-chuncz-msft
Community Support
Community Support

@Anonymous ,

 

You may take a look at the post below.

https://community.powerbi.com/t5/Desktop/Adjusting-Averages-by-a-fixed-percentage/m-p/592427#M281122

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.