Visual Calculations Column Total

Idrissshatila gautampruthi this is to be expected given your DAX - you are testing for the situation where the course group is blank, which is the case in the total row for sure (and also in the case there are any values for unknown course groups). You are effectively saying: whenever course group is blank (which happens in the two cases I just described above) do this calculation, regardless of the current context. What you actually wanted is something else though - you wanted to calculate just the visual total of whatever was the target.

We have specifically designed ISATLEVEL (https://learn.microsoft.com/en-us/dax/isatlevel-function-dax) to detect if you are on the total level, but you could still use ISBLANK([Course group]) if you wanted, just keep in mind what I wrote above.

Anyway, here's a solution to what you are looking for using a visual calculation:
1. Add a visual calculation that indicates the target per course group. I didn't bother to add all course groups to the statement here, but you can easily add the others:

Target_Intermediate = 

SWITCH(

    TRUE,

    [Course group] = "First Degree", 704,

    [Course group] = "First Degree with Fnd Yr", 220

)

 

2. Add another visual calculation that uses IFISATLEVEL and EXPAND to return the total row value you wanted:

Target = IF(ISATLEVEL([Course group]), [Target_Intermediate], EXPAND(SUM([Target_Intermediate]), ROWS))

You need to do this in two steps because recursion is not allowed (i.e. a measure or visual calculation cannot refer to itself).

3. Add your Distance to Target visual calculation. Your example did not show where you got the 'sum' from so I just calculated the difference between the first column with fake data I added and the target. I assume you want to do something more intelligent than this though:

Distance To Target = ROUND([Sum] - [Target],0)

 

4. Finish up by hiding 'Target_Intermediate' column.

 

End result (with less course groups and columns, but idea is the same):

 

 

And done. Happy to help and thanks for trying our visual calculations!