Forum Discussion

Ibrahim_shaik's avatar
Ibrahim_shaik
Icon for Helper V rankHelper V
2 years ago
Solved

Need Help in Creating a Column/measure in a Table

Hello Power BI Community,

 

I want to create a column/measure which has subtracted values from the other column in a table.

 

I have a table with date, time, number1, number2 columns. I want to know the difference between two values in number1 column for example m1, m2, m3 are the values, I want to know the difference between m2-m1, m3-m2 like this for the all the values in the column and for the rest of the columns as well.

 

Note: Here I want to Subtract Values within the Column not from a adjacent column.

 

Please give a solution to this. Should i create a calculated column or measure?

 

Please suggest.

 

Thanks&Regards,

Ibrahim

  • Dangar332's avatar
    Dangar332
    2 years ago

    Hi, Ibrahim_shaik 
    Ibrahim_shaik  use below 

    use below code for new column

     

     

    new = 
var a = 'Table'[number1]
var b = CALCULATE(
            MIN('Table'[number1]),
            OFFSET(1,ORDERBY('Table'[date],asc,'Table'[time],ASC)),
            ALLEXCEPT('Table','Table'[date],'Table'[time])
        )+0
RETURN
b-a

     

     

     

    and for measure use below code

     

    Measure = 
var a = MIN('Table'[number1])
var b = CALCULATE(
           MIN('Table'[number1]),
           OFFSET(1,ALL('Table'[date],'Table'[time]),
           ORDERBY(MIN('Table'[date]),ASC,MIN('Table'[time]),ASC)),
           ALLEXCEPT('Table','Table'[date],'Table'[time])
        )+0
RETURN
b-a

     

     

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

  • Dangar332's avatar
    Dangar332
    2 years ago

    hi, Ibrahim_shaik 

    means you want to replace -25 with 0 and 
    if yes then use below column code 

     

    new = 
var a = 'Table'[number1]
var b = CALCULATE(
            MIN('Table'[number1]),
            OFFSET(1,ORDERBY('Table'[date],asc,'Table'[time],ASC)),
            ALLEXCEPT('Table','Table'[date],'Table'[time])
        )+0

RETURN
IF(b-a<0,0,b-a)

     

    it replace negative value wwith zero(0)

     

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

     

11 Replies

  • Dangar332's avatar
    Dangar332
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    Hi, Ibrahim_shaik 
    Ibrahim_shaik  use below 

    use below code for new column

     

     

    new = 
var a = 'Table'[number1]
var b = CALCULATE(
            MIN('Table'[number1]),
            OFFSET(1,ORDERBY('Table'[date],asc,'Table'[time],ASC)),
            ALLEXCEPT('Table','Table'[date],'Table'[time])
        )+0
RETURN
b-a

     

     

     

    and for measure use below code

     

    Measure = 
var a = MIN('Table'[number1])
var b = CALCULATE(
           MIN('Table'[number1]),
           OFFSET(1,ALL('Table'[date],'Table'[time]),
           ORDERBY(MIN('Table'[date]),ASC,MIN('Table'[time]),ASC)),
           ALLEXCEPT('Table','Table'[date],'Table'[time])
        )+0
RETURN
b-a

     

     

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

    • Ibrahim_shaik's avatar
      Ibrahim_shaik
      Icon for Helper V rankHelper V
      2 years ago

      Hi Dangar332 ,

       

      Thank you so much for the quick response.

       

      I thought of creating an another calculated column for index and then with the help of that I'll do the subtraction within the column but the DAX code you have shared doesn't require any other columns it's very interesting and I learned from this.

       

      I have used the DAX for the Calculated Column and it works fine.

       

      Thanks alot.

    • Ibrahim_shaik's avatar
      Ibrahim_shaik
      Icon for Helper V rankHelper V
      2 years ago

      Here in the measure column what is happening is the measuring is adding all the "4" 5 integers and subtracting with the last value -25 and the result is ABS(20-25) = 5 but I don't want to subtract the sum of upper values with the last value. that is not the correct summation right.

       

      I understand the last value is showing the same value as there are no other values below to subtract with and show the actual value, so it is showing as -25. But for that can we put a condition if there are no other values below to subtract just show as 0.

       

      And Continue to subtract when there are new values below to subtract when the new data comes in the table.

      • Dangar332's avatar
        Dangar332
        Icon for Resident Rockstar rankResident Rockstar
        2 years ago

        hi, Ibrahim_shaik 

        means you want to replace -25 with 0 and 
        if yes then use below column code 

         

        new = 
var a = 'Table'[number1]
var b = CALCULATE(
            MIN('Table'[number1]),
            OFFSET(1,ORDERBY('Table'[date],asc,'Table'[time],ASC)),
            ALLEXCEPT('Table','Table'[date],'Table'[time])
        )+0

RETURN
IF(b-a<0,0,b-a)

         

        it replace negative value wwith zero(0)

         

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

         
  • Ibrahim_shaik's avatar
    Ibrahim_shaik
    Icon for Helper V rankHelper V
    2 years ago

    Hi Dangar332 ,

     

    the calculated column code give the last column as it is in the subtracted column.

    I want the subtracted value but it takes last value same in the subtracted column what should I change in the code?

     

     

    And the last value is adding up in the summation which is not correct.

    • Dangar332's avatar
      Dangar332
      Icon for Resident Rockstar rankResident Rockstar
      2 years ago

      hi, Ibrahim_shaik 

       

      you need substracted column sum in total?
      or something else can you elaborate 

      • Ibrahim_shaik's avatar
        Ibrahim_shaik
        Icon for Helper V rankHelper V
        2 years ago

        Hi Dangar332 ,

         

        I need a Subracted Column and the Subtracted column values SUM.

         

        I have added ABS(b-a) in the DAX to get a Positive Value as I need sum of the subtracted values