Forum Discussion

zivhimmel's avatar
zivhimmel
Resolver I
9 years ago
Solved

Normalize values based on the first one

Hi, I'm trying to display a graph that shows the performance of a portfolio. Currently it looks like this : As you can see, the first value displayed is 101.63, because this is the value of t...
Need Help
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    Hi zivhimmel

     

    I would do something like this:

     

    (I'm calling your fact table Data and assuming you have a related calendar table called Calendar with the relationship on the Date column, and a base measure called [Value] )

     

    1. Define a measure [Value First Date]
      Value First Date =
/* Optional check: Only return Value First Date up to the max date that actually appears in the fact table */
IF (
    MIN ( 'Calendar'[Date] ) <= CALCULATE ( MAX ( Data[Date] ), ALL ( Data ) ),
    CALCULATE (
        [Value],
        CALCULATETABLE ( FIRSTDATE ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) )
    )
)
    2. Define a [Value Normalized] measure:
      Value Normalized =
DIVIDE ( [Value], [Value First Date] ) * 100
OwenAuger's avatar
OwenAuger
Super User
9 years ago

Hi zivhimmel

 

I would do something like this:

 

(I'm calling your fact table Data and assuming you have a related calendar table called Calendar with the relationship on the Date column, and a base measure called [Value] )

 

  1. Define a measure [Value First Date]
    Value First Date =
/* Optional check: Only return Value First Date up to the max date that actually appears in the fact table */
IF (
    MIN ( 'Calendar'[Date] ) <= CALCULATE ( MAX ( Data[Date] ), ALL ( Data ) ),
    CALCULATE (
        [Value],
        CALCULATETABLE ( FIRSTDATE ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) )
    )
)
  2. Define a [Value Normalized] measure:
    Value Normalized =
DIVIDE ( [Value], [Value First Date] ) * 100
  • zivhimmel's avatar
    zivhimmel
    Resolver I
    9 years ago

    OwenAuger, thank you so much. Awesome solution !

     

     

    • autodafe's avatar
      autodafe
      Resolver I
      9 years ago

      Very Nice Example!
      I would need as similar method that calculatyes 100 as ANY first date taken in to account as starting date, and then normalize values accordingly. Is it possible with DAX ?

    • autodafe's avatar
      autodafe
      Resolver I
      9 years ago

      Excellent!

      I would nee to set the value to 100 to ANY first date taken into account for analysis and then normalize subsequent values accordingly.

      Is this possible in DAX?