Ratio with applied filter

[BlueTeam]

I have built a simple DAX ratio using DistintCount, which works as intended.  Problem is when I apply a filter that is related to the numerator.  This is a simple binary filter of yes/no and thus there should be only two answers, 100% or 0% but I get wonky (technical term) results.  What did I miss?

DATA:

Total Records=6346

BLUE Records=2325

Ratio=0.366 or 36.6%

DAX FORMULA:

% BLUE = Calculate(DISTINCTCOUNT('Center'[id]), 'Center'[BLUE]="Yes") / DISTINCTCOUNT('Center'[id])

RESULTS:

1. When I set the filter to, "Yes", I get 100%.

2. When I set the filter to, "No", I get 57.8%

What am I doing wrong?  I've build a lot of DAX ratio calculations into this model that all worked fine until I started adding filters.  Again, it only appears to get wonky when the filter is the numerator.

Thanks!

FILTER: Blue column of yes/no

[SteveCampbell · Accepted solution]

As you have an expression filter in the numerator, this is overriding the slicer. The denominator does not have this, so the slicer is being applied to the denominator only.

Try:

% BLUE = 
Divide(
   Calculate(DISTINCTCOUNT('Center'[id]), 'Center'[BLUE]="Yes") 
   ,
   Calculate(DISTINCTCOUNT('Center'[id]), ALL('Center'[BLUE]) )
)

Love hearing about Power BI tips, jobs and news?
I love to share about these - connect with me!

Stay up to date on  
Read my blogs on  

Remember to spread knowledge in the community when you can! 

[BlueTeam]

That seems to fix the results and the filter has no impact.  Here is another DAX with the same weird results with gender identity.  I've tried it on another measure for gender identity and get the same fixed ratio.

% Male = Divide( Calculate(DISTINCTCOUNT('FTE by Center'[id]), 'FTE by Center'[Gender_Identify]="Male") , Calculate(DISTINCTCOUNT('FTE by Center'[id]), ALL('FTE by Center'[Gender_Identify]) ) )

If I change the last ALL statement to use ID instead, I'm back to the same results as where I started.

Here is another example:

% Male = Calculate(DISTINCTCOUNT('FTE by Center'[id]), 'FTE by Center'[Gender_Identify] = "Male") / DISTINCTCOUNT('FTE by Center'[id])

Filter Results:

Yes=100%

No=107%

[SteveCampbell]

Maybe I have misunderstood. What is the desired result - to get 100% when yes is selcted and 0% for no?

[BlueTeam]

Exactly.  However, it is more complicated than that.  I have lots of demographic data on persons in a table and my customers want to slice and dice using multiple filters.  I'm using a multi-row card for quick facts as customers slice their data and the results are driving me crazy.  If I create a measure of distinctcount for gender="male" and filter to "female", I get a result of 0.  So why doesn't the ratio calculation show 0?  I'm baffled.