Count number of players based on slicer and row filter

[Anonymous]

Hi, thanks a lot for looking at this issue. Really appreciate anyone can help me to solve this issue. 

Here is the data first. 

Player	Game name	Playthrough_ID
Tom	A	56
Tom	A	57
Tom	A	58
Tom	B	59
Tom	C	60
Tom	C	61
Mary	A	62
Mary	A	63
Mary	B	64
Mary	C	65
Mary	A	66
Mary	B	67
Tom	B	68
Monica	C	69
Monica	C	70
Monica	C	71
Monica	B	72
Monica	A	73
Monica	A	74
Mary	C	75
Victoria	A	76
Victoria	B	77
Victoria	B	78
Victoria	A	79
Victoria	A	80
Victoria	C	81
Victoria	C	82
Sarah	C	83
Sarah	C	84
Sarah	A	85
Sarah	A	86
Sarah	B	87
Sarah	B	88

Ideally, create two measures and put in the matrix:

MaxPlaythroughID = MAX('Table'[Playthrough_ID]) 

MaxPlaythroughID_Allselect = CALCULATE([MaxPlaythroughID]; ALLSELECTED('Table'[Game Name])) 

For each game, I would like to know how many players which have the same values (MaxPlaythroughID = MaxPlaythroughID_Allselect).

Thus, Game A has 2 players (Monica and Sarah) and Game C has 3 players (Mary, Sarah and Tom).

Then I want to replicate this result in a stacked bar chart which puts Game name in the Axis and the measure - # users dropoff in values in the value. 

What I write is 

# users dropoff =

      VAR table1 = SUMMARIZE('Table';

                                             'Table'[Player];

                                             "MaxPI"; [MaxPlaythroughID];

                                             "MaxPI_A"; [MaxPlaythroughID_Allselect])

RETURN CALCULATE(DISTINCTCOUNT('Table'[Player]); FILTER(table1; [MaxPI] = [MaxPI_A]))

Then this Stacked bar chart is not correct as I expect. They all output 5 players in both A and C. 

Please anyone can help me to check what is wrong with this measure - # users dropoff in values. How can i change it to get the expected result (A game = 2 players; C game = 3 players). Thanks a lot!

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create measures

Measure =
IF (
    CALCULATE (
        MAX ( Sheet11[Playthrough_ID] ),
        ALLEXCEPT ( Sheet11, Sheet11[Player], Sheet11[Game name] )
    )
        = CALCULATE (
            MAX ( Sheet11[Playthrough_ID] ),
            FILTER ( ALLSELECTED ( Sheet11 ), Sheet11[Player] = MAX ( Sheet11[Player] ) )
        ),
    1,
    0
)

Measure 2 = CALCULATE(DISTINCTCOUNT(Sheet11[Player]),FILTER(ALLEXCEPT(Sheet11,Sheet11[Game name]),[Measure]=1))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks a lot!

[CheenuSing]

Hi @Anonymous ,

Are you attempting to find out

The Number of Players by Game who have played same number of times ?

From your Data

A-Played 2 Times - 2 Players

A-Played 3 Times - 3 Players

B- Played 1 TIme - 1 Player

B- Played 2 times - 4 Players

C-Played 2 Times - 3 Players

C-Palyed 3 Times - 2 Players

If this is so create a summary table

ByGamePlayerCounts = SUMMARIZE('PlayersData',PlayersData[Game],'PlayersData'[Player],"Count",COUNTROWS(PlayersData) )

Link this table with your source table on Game on either table.

Put the Count as axis , Game as Legend and Player as values which will be

automatically CountofPlayers

Cheers

CheenuSing

[Anonymous]

Thank you so much for looking at this issue.