Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
khallar
Regular Visitor

Measure not adjusting correctly to filter context

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

 

  • Date table, with the CPI Index to adjust by inflation
  • Facts table with product, quantity, unit cost, etc
  • Catalog
  • Mapping table where I bring all lines in facts to the corresponding catalog product
  • Country Key
This is what I came up with

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!

2 REPLIES 2
khallar
Regular Visitor

Example of what Im saying
Product A

khallar_1-1651433767796.png

 


Product B

khallar_2-1651433814414.png

 


Product A and B together not working

khallar_0-1651433747118.png

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.

vkalyjmsft_0-1651738544053.png

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.

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.