Need Help in Creating a Column/measure in a Table

[Ibrahim_shaik]

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 · Accepted solution]

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]

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]

hi, @Ibrahim_shaik 

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

[Ibrahim_shaik]

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

[Dangar332]

hi, @Ibrahim_shaik 

i think for that you need to use measure code
use below measure 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
var c= SUMX('Table', 
            var a = '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
            abs(b-a)
        )    
RETURN
IF(ISINSCOPE('Table'[date]),b-a,c)

see below image

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! 

[Dangar332 · Accepted solution]

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]

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 · Accepted solution]

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]

Everything is fine with the DAX but the issue comes when the subtraction happens between lower value - higher value.

Example: 43-53 = -10

with the DAX what it does is it will give the value as 0 so in the IF condition I have changed it to if (b-a<0, ABS(b-a), b-a).

But the Issue with this is in the last value of the column as it has no other value below to subtract so it is in - negative and considering it as + positive and the same value is coming into the subtracted column value and its getting added up in the summation of the subtracted column.

Basically I don't want to consider the last value of the column in the summation.

Can I Put a condition with the Index column where it takes the summation of n-1 column? 

[Dangar332]

hi, @Ibrahim_shaik 

as i know it not posiible in column 

so use below measure

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
var c= SUMX('Table', 
            var a = '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
            IF(b-a<0,0,b-a)
        )    
var d = IF(ISINSCOPE('Table'[date]),b-a,c)
return
d

[Ibrahim_shaik]

Hi @Dangar332 ,

This Measure is Perfect.

But I have three Columns for which I have to do this calculation and I cannot use measure to calculate for three columns the visual will not take three measures.

It shows the "Visual resources are Exceeded".

I only have to Use Calculated Column to calculate three columns.

Measure is good to calcluate the difference for 1 column but as you know I have three columns HVAC, Other, Total.

how to use calculated column for the same result.

[Ibrahim_shaik]

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.