Create % Change Driver over Time Visual

[danb]

Hi, 

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 the base data looks like.

This is what my matrix visual 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%. 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. 

Any help on shedding some light on how to do this is greatly appreciated! 

Thanks

Dan

[Greg_Deckler]

If you could post your raw data only in a form that can be copied and pasted easily, that would be very beneficial.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[danb]

@Greg_Deckler,

Here you go. Thanks!

Date	Index Item	Category	Volume	Price per UOM	Extended Spend
1/1/2018	Walnut Brownie	Bakery	47,569	$                     2.55	$                       121,086
2/1/2018	Walnut Brownie	Bakery	47,569	$                     2.54	$                       120,834
3/1/2018	Walnut Brownie	Bakery	47,569	$                     2.54	$                       120,991
4/1/2018	Walnut Brownie	Bakery	47,569	$                     2.55	$                       121,285
5/1/2018	Walnut Brownie	Bakery	47,569	$                     2.55	$                       121,292
6/1/2018	Walnut Brownie	Bakery	47,569	$                     2.61	$                       124,092
7/1/2018	Walnut Brownie	Bakery	47,569	$                     2.61	$                       124,099
8/1/2018	Walnut Brownie	Bakery	47,569	$                     2.61	$                       124,106
9/1/2018	Walnut Brownie	Bakery	47,569	$                     2.61	$                       124,114
10/1/2018	Walnut Brownie	Bakery	47,569	$                     2.61	$                       124,344
11/1/2018	Walnut Brownie	Bakery	47,569	$                     2.62	$                       124,634
12/1/2018	Walnut Brownie	Bakery	47,569	$                     2.63	$                       124,962
1/1/2018	Bun, Burger	Bakery	135,203	$                     1.26	$                       169,950
2/1/2018	Bun, Burger	Bakery	135,203	$                     1.26	$                       169,923
3/1/2018	Bun, Burger	Bakery	135,203	$                     1.26	$                       170,193
4/1/2018	Bun, Burger	Bakery	135,203	$                     1.24	$                       168,065
5/1/2018	Bun, Burger	Bakery	135,203	$                     1.24	$                       168,083
6/1/2018	Bun, Burger	Bakery	135,203	$                     1.24	$                       168,101
7/1/2018	Bun, Burger	Bakery	135,203	$                     1.24	$                       168,119
8/1/2018	Bun, Burger	Bakery	135,203	$                     1.24	$                       168,137
9/1/2018	Bun, Burger	Bakery	135,203	$                     1.24	$                       168,155
10/1/2018	Bun, Burger	Bakery	135,203	$                     1.25	$                       168,721
11/1/2018	Bun, Burger	Bakery	135,203	$                     1.25	$                       169,433
12/1/2018	Bun, Burger	Bakery	135,203	$                     1.26	$                       170,238
1/1/2018	Chocolate Brownie	Bakery	109,724	$                     1.82	$                       199,347
2/1/2018	Chocolate Brownie	Bakery	109,724	$                     1.82	$                       199,534
3/1/2018	Chocolate Brownie	Bakery	109,724	$                     1.82	$                       199,479
4/1/2018	Chocolate Brownie	Bakery	109,724	$                     1.82	$                       199,678
5/1/2018	Chocolate Brownie	Bakery	109,724	$                     1.82	$                       199,687
6/1/2018	Chocolate Brownie	Bakery	109,724	$                     1.81	$                       198,686
7/1/2018	Chocolate Brownie	Bakery	109,724	$                     1.81	$                       198,694
8/1/2018	Chocolate Brownie	Bakery	109,724	$                     1.81	$                       198,703
9/1/2018	Chocolate Brownie	Bakery	109,724	$                     1.81	$                       198,712
10/1/2018	Chocolate Brownie	Bakery	109,724	$                     1.81	$                       198,982
11/1/2018	Chocolate Brownie	Bakery	109,724	$                     1.82	$                       199,323
12/1/2018	Chocolate Brownie	Bakery	109,724	$                     1.82	$                       199,708
1/1/2018	Bun, Brioche	Bakery	522,197	$                     1.25	$                       654,365
2/1/2018	Bun, Brioche	Bakery	522,197	$                     1.26	$                       656,088
3/1/2018	Bun, Brioche	Bakery	522,197	$                     1.26	$                       655,514
4/1/2018	Bun, Brioche	Bakery	522,197	$                     1.25	$                       650,518
5/1/2018	Bun, Brioche	Bakery	522,197	$                     1.25	$                       650,587
6/1/2018	Bun, Brioche	Bakery	522,197	$                     1.25	$                       650,656
7/1/2018	Bun, Brioche	Bakery	522,197	$                     1.25	$                       650,725
8/1/2018	Bun, Brioche	Bakery	522,197	$                     1.25	$                       650,794
9/1/2018	Bun, Brioche	Bakery	522,197	$                     1.25	$                       650,864
10/1/2018	Bun, Brioche	Bakery	522,197	$                     1.25	$                       653,028
11/1/2018	Bun, Brioche	Bakery	522,197	$                     1.26	$                       655,747
12/1/2018	Bun, Brioche	Bakery	522,197	$                     1.26	$                       658,822
1/1/2018	Butter Alternative, Liquid	Oils	258,333	$                     0.63	$                       162,750
2/1/2018	Butter Alternative, Liquid	Oils	258,333	$                     0.63	$                       162,983
3/1/2018	Butter Alternative, Liquid	Oils	258,333	$                     0.63	$                       163,060
4/1/2018	Butter Alternative, Liquid	Oils	258,333	$                     0.64	$                       165,596
5/1/2018	Butter Alternative, Liquid	Oils	258,333	$                     0.64	$                       165,613
6/1/2018	Butter Alternative, Liquid	Oils	258,333	$                     0.64	$                       165,630
7/1/2018	Butter Alternative, Liquid	Oils	258,333	$                     0.65	$                       168,230
8/1/2018	Butter Alternative, Liquid	Oils	258,333	$                     0.65	$                       168,248
9/1/2018	Butter Alternative, Liquid	Oils	258,333	$                     0.65	$                       168,265
10/1/2018	Butter Alternative, Liquid	Oils	258,333	$                     0.65	$                       168,801
11/1/2018	Butter Alternative, Liquid	Oils	258,333	$                     0.66	$                       169,476
12/1/2018	Butter Alternative, Liquid	Oils	258,333	$                     0.66	$                       170,238

[danb · Accepted solution]

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.