Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi all, I want to calculate historic savings considering inflation. I managed to do it by product by year, but it breaks when I add any other parameter like country or more than one product. I have the following model
To get the weighted average price
PTrendQ = SUM(‘FACTS’[Quantity])
Price Trend WA =
CALCULATE(
DIVIDE(
SUMX( ‘FACTS’, ‘FACTS’[Unit Cost USD] * ‘FACTS’[Quantity]),
[PTrendQ]),
FILTER(‘FACTS’,’FACTS’[Unit Cost USD]<>0))
To get the weighted average price for last year, and if not available the year before
PTrend LY 1 = calculate([Price Trend WA],SAMEPERIODLASTYEAR(DateKey[Date]))
PTrend LY 2 =
if( [PTrend LY 1]=BLANK() , calculate([Price Trend WA],PARALLELPERIOD(DateKey[Date],-2,YEAR)) , [PTrend LY 1])
To get the adjusted unit cost at each period
U Cost ADJ =
VAR CPITOUSE = CALCULATE(AVERAGE(DateKey[CPI Index]),SAMEPERIODLASTYEAR(DateKey[Date]))
VAR MULTIPLIER = divide (CPITOUSE,MAX(DateKey[CPI Index]),1)
VAR ADJUCOST = calculate(average(‘FACTS’[Unit Cost USD]),FILTER(‘FACTS’,’FACTS’[Unit Cost USD]<>0)) * MULTIPLIER
RETURN ADJUCOST
To calculate the savings
Savings INTERMEDIATE =
if( or( [PTrend LY 2] = BLANK(), SUM(‘FACTS’[Unit Cost USD])=0), BLANK() ,
- sum(‘FACTS’[Quantity]) * ('Measures 5'[U Cost ADJ] - [PTrend LY 2]))
And to fix the total row I thought of using SUMX to adjust for the year context
Savings year =
SUMX(VALUES(DateKey[Year]), 'Measures 5'[Savings INTERMEDIATE])
It seems to be working fine product by product and by year, but it does not work when I want to add more than one product, or add client that is buying, etc. Any ideas on how to work around this?
Thanks!
Example of what Im saying
Product A
Product B
Product A and B together not working
I need something that calculates this line by line as shown, but then be able to aggregate by year with all products, by category and by client. Any help appreciated, thanks!
Hi @khallar ,
Firstly please check that in the Values pane of the visual, Quantity has sum selected.
If that doesn't work, from now on, I need to reproduce your problem to know what the cause is. So more information is needed.
According to your description, there are two tables: FACTS and DateKey. In the FACTS table, there are two columns: Quantity and Unit Cost USD. In the DateKey table, there are three columns: Date, CPI Index and Year.
How does the two tables relate to each other, and which table does the Product in, and what's the relationship with other table.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
145 | |
79 | |
64 | |
52 | |
47 |
User | Count |
---|---|
217 | |
89 | |
76 | |
67 | |
60 |