Visual Calculations Column Total

[gautampruthi]

Hi,

I am using visual calculation in a matrix to do some calculations but the column total is not changing if user filters out data

1. Target =

    SWITCH(

        TRUE(),

        ISBLANK([Course Group]), 704 + 220 + 17 + 120 + 30 + 153 + 149 + 60,

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

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

        [Course Group] = "Other Undergraduate", 17,

        [Course Group] = "Primary ITE", 120,

        [Course Group] = "Secondary ITE", 30,

        [Course Group] = "Primary PGCE", 153,

        [Course Group] = "Secondary PGCE", 149,

        [Course Group] = "Postgraduate", 60,

        0

    )

 2. Distance to Target = ROUND([Sum] - [Target], 0)

Dummy Data

Issue

As Shown in the image the Target is showing as 1453 instead of 220 and Distrance to Target as -1255 instead of -22

[gautampruthi]

Thanks @jeroenterheerdt for the help.

[jeroenterheerdt · Accepted solution]

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

[Greg_Deckler]

@gautampruthi Do you have to use a Visual Calculation? Seems like you could do what you are doing in a traditional measure. Visual calculations are a preview feature, which means they are buggy and unsupported. So no surprise you are having issues. I would stick to traditional measures. 

First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[gautampruthi]

Tried removing  isblank condition from target then  blank gets displayed instead of correct sum

[Idrissshatila]

@jeroenterheerdt can you help ?

[jeroenterheerdt]

thanks for looping me in!