topic Re: Need Help in Creating a Column/measure in a Table in DAX Commands and Tips

Need Help in Creating a Column/measure in a Table

Ibrahim_shaik — Wed, 06 Mar 2024 04:52:46 GMT

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

Re: Need Help in Creating a Column/measure in a Table

Dangar332 — Wed, 06 Mar 2024 08:04:57 GMT

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

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

Re: Need Help in Creating a Column/measure in a Table

Ibrahim_shaik — Wed, 06 Mar 2024 08:31:50 GMT

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.

Re: Need Help in Creating a Column/measure in a Table

Ibrahim_shaik — Thu, 07 Mar 2024 07:29:38 GMT

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.

Re: Need Help in Creating a Column/measure in a Table

Dangar332 — Thu, 07 Mar 2024 09:27:00 GMT

hi, Ibrahim_shaik

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

Re: Need Help in Creating a Column/measure in a Table

Ibrahim_shaik — Thu, 07 Mar 2024 09:34:27 GMT

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

Re: Need Help in Creating a Column/measure in a Table

Dangar332 — Thu, 07 Mar 2024 10:33:12 GMT

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

Re: Need Help in Creating a Column/measure in a Table

Ibrahim_shaik — Thu, 07 Mar 2024 11:19:48 GMT

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.

Re: Need Help in Creating a Column/measure in a Table

Dangar332 — Thu, 07 Mar 2024 13:38:16 GMT

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.

Re: Need Help in Creating a Column/measure in a Table

Ibrahim_shaik — Sat, 09 Mar 2024 07:01:31 GMT

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?

Re: Need Help in Creating a Column/measure in a Table

Dangar332 — Sun, 10 Mar 2024 07:45:21 GMT

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

Re: Need Help in Creating a Column/measure in a Table

Ibrahim_shaik — Mon, 11 Mar 2024 05:48:56 GMT

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.