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!
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])
Solved! Go to Solution.
@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
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
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
@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
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |