topic Re: How to calculate difference between two columns - related column and count column in DAX Commands and Tips

How to calculate difference between two columns - related column and count column

Anonymous — Fri, 23 Aug 2019 13:00:44 GMT

I'm struggling with this. I want to create a column that calculates the difference of (First RequiredShiftA) and (CountDuringShiftA) columns.

This started as two tables. I created new columns in my larger table to add the related columns of the required roles, shift a, and shift b thinking that once they were in the same table it would be easy to just subtract one column from the other - nope.

Re: How to calculate difference between two columns - related column and count column

ibarrau — Fri, 23 Aug 2019 17:13:58 GMT

Hi. I think I might me missing something. Did you try creating a new calculated dax column on the big table with the following formula?

Table[Column1] - Table[Column2]

Regards

Re: How to calculate difference between two columns - related column and count column

Anonymous — Fri, 23 Aug 2019 17:25:52 GMT

I did try that but the results are no where near correct.

Re: How to calculate difference between two columns - related column and count column

Anonymous — Fri, 23 Aug 2019 17:30:50 GMT

For example, using that formula:

instead of 2 - 2 = 0

I am getting -6

So I'm not exactly sure what is being calculated

Re: How to calculate difference between two columns - related column and count column

ibarrau — Fri, 23 Aug 2019 17:40:02 GMT

Are you sure you are checking the result in the data view?

2 - 2 = 6 sounds like having the visualization with a value of SUM of calculatedColumn.

Can you show a picture with the two columns and the substract result from the DataView?

Re: How to calculate difference between two columns - related column and count column

ibarrau — Fri, 23 Aug 2019 17:42:43 GMT

Are you sure you are checking the result in the data view?

2 - 2 = 6 sounds like having the visualization with a value of SUM of calculatedColumn.

Can you show a picture with the two columns and the substract result from the DataView?

Re: How to calculate difference between two columns - related column and count column

Anonymous — Fri, 23 Aug 2019 17:50:17 GMT

The one column was a quick measure and doesn't show up in the data view.

Re: How to calculate difference between two columns - related column and count column

ibarrau — Fri, 23 Aug 2019 17:52:08 GMT

Then don't use a "quick measure" and create a "calculated column" with the substract.

Re: How to calculate difference between two columns - related column and count column

Anonymous — Fri, 23 Aug 2019 18:04:05 GMT

I've been trying. I get a lot of errors.

Re: How to calculate difference between two columns - related column and count column

Anonymous — Fri, 23 Aug 2019 18:08:05 GMT

I think this is what i'm trying to do - but have no idea - this DAX stuff is crazy.

I basically want a count of all the during roles for shift b that gets subtracted from the # of required roles for shift b.

CALCULATE(

COUNTA('Contact Export PowerBI'[During Section and Role]),

'Contact Export PowerBI'[During Shift] IN { "B Shift" } - 'Contact Export PowerBI'[RequiredShiftB])

Re: How to calculate difference between two columns - related column and count column

ibarrau — Fri, 23 Aug 2019 18:10:09 GMT

Can you send a picture of the data model? I think if we have only three tables with single directional relation we shouldn't be seeing this error.

Re: How to calculate difference between two columns - related column and count column

ibarrau — Fri, 23 Aug 2019 18:24:21 GMT

I don't think I'm following the whole idea. Try to use variables to avoid this kind of error. The formula you are showing looks like a measure. I think this code should help:

VAR counting = COUNTA('Contact Export PowerBI'[During Section and Role]),

VAR in_shift_b = 
CALCULATE (
    countring , 
    'Contact Export PowerBI'[During Shift] = "B Shift" 
)
 VAR number_of_req_roles = COUNT('Contact Export PowerBI'[RequiredShiftB] )

RETURN
in_shift_b - number_of_req_roles

Hope this helps,

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