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

[Anonymous]

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.

[ibarrau]

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

[Anonymous]

For example, using that formula:

instead of 2 - 2 = 0 

I am getting -6

So I'm not exactly sure what is being calculated

[ibarrau]

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?

[Anonymous]

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

[ibarrau]

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

[Anonymous]

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

[ibarrau]

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.

[Anonymous]

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])

[ibarrau]

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.

[ibarrau]

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?

[Anonymous]

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