Excluding responses changing percentage

[Anonymous]

Hi,

I am trying to create a graph that shows the percentage of people choosing each sport as their favourite out of all people who do at least 1 sport. My column 'Fav Combined' has favourite sport for each participant (one per participant and blank if no sport is done at all by the person). I also have a binary variable with 0 if a person does not do any sport and 1 if they do. 

After creating a visual with Fav Combined in both the x and y axis and choosing the option to show as the percentage of total, I am running into an issue where if I exclude one of the sports because of a small sample, all of the percentages change because of it being excluded from the calculations. 

How can I create a graph which is unaffected by excluding sports? I have tried creating new measures but can't get it right.

Thanks for your help!

Julia

[sjoerdvn · Accepted solution]

You haven't shared much information on all tables and columns involved, but I would create two measures, one for the count and one for the average, something like this:

# FAV Combined = COUNT(tbl[Fav Combined])

% Fav Combined = DIVIDE([# FAV Combined]
  , CALCULATE( [# FAV Combined], ALL(tbl[sport]))