Ignore Slicer in Average Measure plus TopN Tiebreakers

[sheidari]

I've been searching for a solution to two problems for the last 3 days and after testing different solutions, I am back to square one.  Hoping someone can help me solve what seem to be easy fixes...I know how to do this in Tableau but PBI doesn't seem as straightforward.

I have sample data in the attached PBI file for a game.  Each player earns points across specific metrics. 

https://drive.google.com/file/d/1xsoMvYXD_i5An33OE30B-FlbKhT07nwu/view?usp=sharing 

Problem 1:

I want to select a player from the slicer and show that persons actual points relative to the team and league averages.  I think i got the league average working but the team average changes each time that i filter on a player...it seems to only take the average of that player vs their entire team.  The average should exclude players with 0 points.

Problem 2:

I want to rank the top 10 players across 4 metrics but sometimes i run into an issue where multiple players are tied...so rather than get a top 10, i get a much higher result.  As a result, the top player points card seems to be taking that top 1 player and multiplying it by the total # of players with 4 points. I want the tiebreaker to be based on alphabetical order of the player name for both the points by player and top player points visuals.  I've tried multiple things using Rankx to no avail so i deleted the metrics to start fresh.  

Any help will be greatly appreciated! This is for a client project and i'm at the last mile before delivering but these two issues are a pain in the butt.

[sheidari]

I have sample data in the attached PBI file for a game.  Each player earns points across specific metrics. 

https://drive.google.com/file/d/1xsoMvYXD_i5An33OE30B-FlbKhT07nwu/view?usp=sharing 

Problem 1:

I want to select a player from the slicer and show that persons actual points relative to the team and league averages.  I think i got the league average working but the team average changes each time that i filter on a player...it seems to only take the average of that player vs their entire team.  The average should exclude players with 0 points.

Problem 2:

I want to rank the top 10 players across 4 metrics but sometimes i run into an issue where multiple players are tied...so rather than get a top 10, i get a much higher result.  As a result, the top player points card seems to be taking that top 1 player and multiplying it by the total # of players with 4 points. I want the tiebreaker to be based on alphabetical order of the player name for both the points by player and top player points visuals.  I've tried multiple things using Rankx to no avail so i deleted the metrics to start fresh.  

Any help will be greatly appreciated! This is for a client project and i'm at the last mile before delivering but these two issues are a pain in the butt.

[MartynRamsden]

Hi @sheidari 

The following measure should give you the answer to problem 1:

Team Avg =
VAR SelTeam = SELECTEDVALUE ( 'Points Transactions'[team_key] )
VAR Result =
    AVERAGEX (
        FILTER (
            ALL ( 'Points Transactions' ),
            'Points Transactions'[team_key] = SelTeam
                && 'Points Transactions'[Points] <> 0
        ),
        'Points Transactions'[Points]
    )
RETURN
    Result

With regards to problem 2, are you able to show us your desired result?

Best regards,

Martyn

If I answered your question, please help others by accepting it as a solution. 

[sheidari · Accepted solution]

@MartynRamsden Thanks for problem 1's solution.  It finally works!

For problem 2, i'm looking for two results.  I've attached the images!

https://drive.google.com/file/d/1wrE8gAsMvSs9PYjrbvKtBts8XKIOC1vs/view?usp=sharing 

[sheidari]

Hi @MartynRamsden just following up to see if you had a chance to look at the end result for problem #2?  Also, if anyone else has any ideas, it would be greatly appreciated. 

Thanks!

[sheidari]

Figured out problem #2. Thanks again for the help on the first one!