Forum Discussion

khallar's avatar
khallar
Regular Visitor
4 years ago

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!

Need Help

2 Replies

  • khallar's avatar
    khallar
    Regular Visitor
    4 years ago

    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!

    • v-yanjiang-msft's avatar
      v-yanjiang-msft
      Community Support
      4 years ago

      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.