cancel
Showing results 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.

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

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

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

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!

Community Support

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.

Announcements

#### 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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors