Forum Discussion
Help with defining starting row value
Hello! I am new to the forum and am stuck trying to come up with a formula/measure. I am needing to index 3 different data sets in order to align them on the same scale instead of graphing with multiple axis. I know what formula I need to be using but the issue I am having is defining the initial value. I want to be able to use a filter slicer to graph different date ranges of the data so the initial value would change depending on the date selected. You can see from the 2 different charts (modeled in excel) that even with a secondary axis the data sets are too different to plot effectively (item 3 is too small to plot). By indexing the values, I am able to narrow the scale to show the variations better.
The formula I need is: (Current value/Initial value)*100
Any help would be GREATLY appreciated; thanks!!
You can create three measures (one for each Item) with the following...
Item 1 Indexed Value =
var _initialValueDate =
//gets the minimum week end date from the table for the dates that are SELECTED(Filtered)
MINX(
ALLSELECTED('Table'),
'Table'[Week End Date]
)
var _initialValue =
//gets the Item 1 value that coresponds to the intial date calculated
LOOKUPVALUE(
'Table'[Item 1],
'Table'[Week End Date],
_initialValueDate
)
return
//returns the indexed value using the inital value calculated from the initial date
ROUND(
DIVIDE(
MIN('Table'[Item 1]),
_initialValue,
0
)
*100,
0
)You should end up with...
3 Replies
You can create three measures (one for each Item) with the following...
Item 1 Indexed Value =
var _initialValueDate =
//gets the minimum week end date from the table for the dates that are SELECTED(Filtered)
MINX(
ALLSELECTED('Table'),
'Table'[Week End Date]
)
var _initialValue =
//gets the Item 1 value that coresponds to the intial date calculated
LOOKUPVALUE(
'Table'[Item 1],
'Table'[Week End Date],
_initialValueDate
)
return
//returns the indexed value using the inital value calculated from the initial date
ROUND(
DIVIDE(
MIN('Table'[Item 1]),
_initialValue,
0
)
*100,
0
)You should end up with...
Thank you again for the solution! I was finally able to dedicate time to get all these measures moved into a single table to perform the lookup function and your formula works perfect!
Thank you for the quick reply!
Based on the lookup value needed I am going to have to do some pre-work to get Items 1, 2, & 3 into a column within one of my data tables since they are currently measures. I have a master data table that pulls in values for Items 1-~100 in a single column with each row a daily date. I had to create the measures to get a calculation for Item 1 (for example, using Items (5+10+13+20) - 18, and then additional measure for Items 1, 2, & 3 to calculate the weekly averages. So currently even though all of the values reside in my master data table, I had to create the measures for the desired results. Once I can get them into a table to perform the lookup value, I will be able to try your solution.
Thanks!
