Forum Discussion

FPE5's avatar
FPE5
Regular Visitor
10 months ago
Solved

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

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

 

 

NameTeamPointsTeam share %
Pete

Red

525%
JohnRed1575%
HankBlue10100%

 

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's avatar
    johnt75
    10 months ago

    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's avatar
    tayloramy
    10 months ago

    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.

     

4 Replies

  • johnt75's avatar
    johnt75
    Super User
    10 months ago

    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's avatar
      FPE5
      Regular Visitor
      10 months ago

      Thank you, John!

      This worked like a charm. 

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

  • tayloramy's avatar
    tayloramy
    Super User
    10 months ago

    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's avatar
      FPE5
      Regular Visitor
      10 months ago

      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 😄