Forum Discussion

danb's avatar
danb
Resolver I
8 years ago
Solved

% Change over Time

Hi Everyone! I am struggling to figure out how to build a matrix visual that will show me a weighted average % change of spend over time. I have an example of where my data is at as well as what I wo...
Need Help
  • danb's avatar
    danb
    8 years ago

    After some more research, I was able to solve it by creating two measures.

     

    1. Value First Date = CALCULATE([Extended Spend running total in Date],CALCULATETABLE(FIRSTDATE('Table1'[Date]),ALLSELECTED('Table1'[Date])))

     

    2. Time Period Driver = DIVIDE(sum('Table1'[Extended Spend]),[Value First Date])*1

     

    I then dropped in the "Time Period Driver" measure into the matrix visual and it worked. 

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

Hi danb

 

This is quite possible, but will be easier to suggest a calculation if you share a snippet of your source table.  This will help get the correct syntax for your table and column names.

 

 

  • danb's avatar
    danb
    Resolver I
    8 years ago

    Thanks Phil_Seamark, the source table is pretty straight forward. 

     

    I would like to do the calculation for the "Extended Spend" column so that I can have a weighted average percentage. There are other categories other than dairy with different volumes and pricing and want to be able to roll them all together so show what the overal book of goods pricing trend will do over time. 

     

    Dan

    • danb's avatar
      danb
      Resolver I
      8 years ago

      After some more research, I was able to solve it by creating two measures.

       

      1. Value First Date = CALCULATE([Extended Spend running total in Date],CALCULATETABLE(FIRSTDATE('Table1'[Date]),ALLSELECTED('Table1'[Date])))

       

      2. Time Period Driver = DIVIDE(sum('Table1'[Extended Spend]),[Value First Date])*1

       

      I then dropped in the "Time Period Driver" measure into the matrix visual and it worked.