Forum Discussion

tnevarez's avatar
tnevarez
Frequent Visitor
3 years ago
Solved

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 mul...
Need Help
  • jgeddes's avatar
    jgeddes
    3 years ago

    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...

     

     

tnevarez's avatar
tnevarez
Frequent Visitor
3 years ago

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!