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. 

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.