Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to divide current value by first value in column (Fixed value) per year

Hello Community,

 

I am facing a little DAX problem, I have a what if Calculated measure

 

Price: Sum(price) + Selected (Price Parameter)

 

The result from this, I need to create a measure that basically takes my current value divided by my first value.

 

IndexPrice (Measure Above)Year
11.8702008
21.8402008
31.8652008
41.8802008

 

This a sample of data I have several year and for each year the index restart at 1

 

Index values (1-250) for each year 

 

I need for each year to calculate the current price value in table (index)  / price for 1 index day as below

Price (Index =2) /  Price (Index =1), Price (Index =3) /  Price (Index =1) , Price (Index = 4)/  Price (Index =1) and so on

 

How can I achieve this in dax?

 

Thanks so much!

Need Help
  • FrankAT's avatar
    FrankAT
    5 years ago

    Hi Anonymous 

    you can do it like this:

     

     

    Price calculation = 
VAR _PriceIndex_1 = 
    CALCULATE(
        MIN('Table'[Price]),
        FILTER(
            ALLEXCEPT('Table','Table'[Year]),
            'Table'[Index] = 1
        )
    )
VAR _ActualPrice = MIN('Table'[Price])
RETURN
    DIVIDE(_ActualPrice,_PriceIndex_1)

     

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)

  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    Based on your description, you can do some steps as follows.

     

    1. Create a calculated index column.( I created an index column in advance to mark the existing order.)

    _index =

    RANKX (

        FILTER (

            CASE,

            EARLIER ( CASE[Year] ) = CASE1[Year]

        ),

        'CASE'[Index],

        ,

        ASC

    )

     

    1. Create a ‘Price Calculation’measure.

    Measure = DIVIDE(

    MAX('CASE'[Price (Measure Above)]),

    CALCULATE(

    MAX('CASE'[Price (Measure Above)]),

    FILTER(

    ALLEXCEPT('CASE',CASE1[Year]),

    [_index]=1

    )

    )

    )

     

    Result:

     

     

    Best Regards,

    Yuna

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    5 years ago

    Hello All,

     

    thank you very much for your help,

     

    Fixing my DAX like this finally worked for me

     

    Price Calculation =
    VAR _PriceIndex_1 =
    CALCULATE(MINX(Contracts_Zema,[Price + Price Parameter]),
    FILTER(ALLEXCEPT('Contracts_Zema','Contracts_Zema'[Contract_Year],Contracts_Zema[Location],Contracts_Zema[Contract_Type]),
    'Contracts_Zema'[Trade_Day] = 1
    )
    )
    VAR _ActualPrice = MAXX(Contracts_Zema,[Price + Price Parameter])
    RETURN
    DIVIDE(_ActualPrice,_PriceIndex_1) -1

5 Replies

  • FrankAT's avatar
    FrankAT
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Anonymous 

    you can do it like this:

     

     

    Price calculation = 
VAR _PriceIndex_1 = 
    CALCULATE(
        MIN('Table'[Price]),
        FILTER(
            ALLEXCEPT('Table','Table'[Year]),
            'Table'[Index] = 1
        )
    )
VAR _ActualPrice = MIN('Table'[Price])
RETURN
    DIVIDE(_ActualPrice,_PriceIndex_1)

     

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hey,

       

       

      thank you so much

      For me the results are not working, could it be because I have more dimensions in my data than year? so the all except doesnt work?

       

      I also have location, Season?

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi  

       

      Im not getting the same results. Could it be because my dataset has more fields than year ?

       

      Price Calculation =
      VAR _PriceIndex_1 =
      CALCULATE(
      MIN('Contracts_Zema'[Price]),
      FILTER(ALLEXCEPT('Contracts_Zema','Contracts_Zema'[Contract_Year]),
      'Contracts_Zema'[Trade_Day] = 1
      )
      )
      VAR _ActualPrice = MIN('Contracts_Zema'[Price])
      RETURN
      DIVIDE(_ActualPrice,_PriceIndex_1)

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    Based on your description, you can do some steps as follows.

     

    1. Create a calculated index column.( I created an index column in advance to mark the existing order.)

    _index =

    RANKX (

        FILTER (

            CASE,

            EARLIER ( CASE[Year] ) = CASE1[Year]

        ),

        'CASE'[Index],

        ,

        ASC

    )

     

    1. Create a ‘Price Calculation’measure.

    Measure = DIVIDE(

    MAX('CASE'[Price (Measure Above)]),

    CALCULATE(

    MAX('CASE'[Price (Measure Above)]),

    FILTER(

    ALLEXCEPT('CASE',CASE1[Year]),

    [_index]=1

    )

    )

    )

     

    Result:

     

     

    Best Regards,

    Yuna

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello All,

       

      thank you very much for your help,

       

      Fixing my DAX like this finally worked for me

       

      Price Calculation =
      VAR _PriceIndex_1 =
      CALCULATE(MINX(Contracts_Zema,[Price + Price Parameter]),
      FILTER(ALLEXCEPT('Contracts_Zema','Contracts_Zema'[Contract_Year],Contracts_Zema[Location],Contracts_Zema[Contract_Type]),
      'Contracts_Zema'[Trade_Day] = 1
      )
      )
      VAR _ActualPrice = MAXX(Contracts_Zema,[Price + Price Parameter])
      RETURN
      DIVIDE(_ActualPrice,_PriceIndex_1) -1