Forum Discussion
% 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 would like it to show.
This is what my data looks like so far.
I want to create a measure (i think) that will take the spend values for each month and divide them by the spend value for January 2018. The end result would look like:
The time period will shift however the first month selected will always be the baseline and equal to 100%.
Any help on shedding some light on how to do this is greatly appreciated!
Thanks
Dan
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.
5 Replies
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.
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
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.
I know this is a stupid question, but how do you get the extended spend running total? Can you post the dax formula? Thank you.
Tenzin - i actually cheated and had the cumulative total calculated in the underlying Excel file. I have used this link before on the forum to do the cumulative total before on other projects
