Count # players dropping off on that game

[Anonymous]

Hi, thank you for looking at this issue. Really appreciate anyone can give any suggestion on the issue. 

For example:

Context:

In the dataset, I have 5 players and 3 different games. also include the playthrough_ID.

If the player plays the game, then playthroug_ID would be created. 

Expected result:

# players drop off on the games which the end users selected.

For example, we know 68 is the biggest playthrough_id of Player-Tom and thus, Tom drops off on the Game - B

Then, [Game name] will be put in two different kinds of filters (slicer and row filter of stacked bar chart)

If i select Game A and Game C in the slicer first, Then i created two measures:

MaxPlaythroughID = MAX('Table'[Playthrough_ID]) (will be filtered by row filter of stacked bar chart)

MaxPlaythroughID_Allselect = CALCULATE([MaxPlaythroughID]; ALLSELECTED('Table'[Game Name])) (will be filtered by slicer only rather than row filter of stacked bar chart)

Then we can refer to the matrix first. If MaxPlaythroughID = MaxPlaythroughID_Allselect, we can say that person drops off on that game. 

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

In all, I would like to create a stacked bar chart which puts game name in axis and a measure - # users dropoff in values. 

# users dropoff =

VAR table1 =

SUMMARIZE('Table';

'Table'[Player];

"MaxPI"; [MaxPlaythroughID];

"MaxPI_A"; [MaxPlaythroughID_Allselect])

RETURN

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

But this stacked bar chart always return the wrong value. A should be 2 players and C should have 3 players while they all have 5 players.

So what's wrong with this measure - # users dropoff? anyone have some input?

[Greg_Deckler · Accepted solution]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490