Team share % measure (based on variable) changes in scatterplot to total of all rows

[FPE5]

Hi guys,

I have got a measure that calculates the teamshare % as in example measure and table below.

Team Share % =

var PlayerPoints = CALCULATE(sum('Table'[Points]))

var TeamPoints = CALCULATE(sum('Table'[Points]),ALLEXCEPT('Table', 'Table'[Team]))

Return PlayerTarget/TeamTargets

Name	Team	Points	Team share %
Pete	Red	5	25%
John	Red	15	75%
Hank	Blue	10	100%

This is all fine, but when I try to get this measure in a scatterplot, with Name as Value and without the Team variable, the Team share % changes and combines all the points from all the Names. So Pete would be 16% (5/30 instead of 5/20).

I cant seem to get it to work within a measure. Any tips?

Thanks in advance. 🙂

[johnt75 · Accepted solution]

Rather than using ALLEXCEPT you can use REMOVEFILTERS and VALUES, e.g.

Team Share % =
VAR PlayerPoints =
    CALCULATE ( SUM ( 'Table'[Points] ) )
VAR TeamPoints =
    CALCULATE (
        SUM ( 'Table'[Points] ),
        REMOVEFILTERS ( 'Table' ),
        VALUES ( 'Table'[Team] )
    )
RETURN
    DIVIDE ( PlayerPoints, TeamPoints )

There's articles about this technique at https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/  and https://www.sqlbi.com/articles/using-allexcept-vs-all-values/ 

[tayloramy · Accepted solution]

Hi @FPE5, 

You’re seeing that because your measure removes all filters except Team, and on the scatter the Team column isn’t on the visual. That means your “team total” quietly becomes the grand total (all Names), so Pete’s share turns into 5/30 instead of 5/20.

Here’s a robust pattern that always re-applies the player’s Team and only removes the Name filter when computing the team total (so page/report filters like Date still apply).

 

Team Share % :=
VAR TeamForName = SELECTEDVALUE('Table'[Team])
VAR PlayerPoints = SUM('Table'[Points])
VAR TeamPoints =
    CALCULATE(
        SUM('Table'[Points]),
        REMOVEFILTERS('Table'[Name]),                -- ignore the current Name only
        KEEPFILTERS('Table'[Team] = TeamForName)     -- force the player’s Team
    )
RETURN
IF ( NOT ISBLANK(TeamForName), DIVIDE(PlayerPoints, TeamPoints) )

Use that measure on your scatter (X or Y), with Name as the category/detail. It will return 25% for Pete even if Team isn’t on the visual.

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[FPE5]

Hi Tayloramy,

Thank you for your reply! 

I've recreated it with some extra fields (which I didnt share like Date), and it works great too. 

I'm still a bit puzzled on the use of 

VAR TeamForName = SELECTEDVALUE('Table'[Team])

 and the KEEPFILTERS addition to force the team. So I will read into that as well.

Thank you for your help 😄

[johnt75 · Accepted solution]

Rather than using ALLEXCEPT you can use REMOVEFILTERS and VALUES, e.g.

Team Share % =
VAR PlayerPoints =
    CALCULATE ( SUM ( 'Table'[Points] ) )
VAR TeamPoints =
    CALCULATE (
        SUM ( 'Table'[Points] ),
        REMOVEFILTERS ( 'Table' ),
        VALUES ( 'Table'[Team] )
    )
RETURN
    DIVIDE ( PlayerPoints, TeamPoints )

There's articles about this technique at https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/  and https://www.sqlbi.com/articles/using-allexcept-vs-all-values/ 

[FPE5]

Thank you, John!

This worked like a charm. 

Thanks aswell for the two links, im gonna dive into these 🙂