Forum Discussion
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] .
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):
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])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
3 Replies
- v-chuncz-msft
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
- ed_mcdill
Advocate II
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
- AnonymousNot 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