cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Egor_Paulig_RUS Frequent Visitor
Frequent Visitor

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

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: DAX calculation of variable inside iterator SUMX

@Egor_Paulig_RUS ,

 

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.
3 REPLIES 3
Highlighted
Community Support Team
Community Support Team

Re: DAX calculation of variable inside iterator SUMX

@Egor_Paulig_RUS ,

 

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.
ed_mcdill Regular Visitor
Regular Visitor

Re: DAX calculation of variable inside iterator SUMX

Hi @Egor_Paulig_RUS 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 

Egor_Paulig_RUS Frequent Visitor
Frequent Visitor

Re: DAX calculation of variable inside iterator SUMX

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