Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Ratio with applied filter

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

 

 

Need Help
  • SteveCampbell's avatar
    SteveCampbell
    7 years ago

    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! 

4 Replies

  • SteveCampbell's avatar
    SteveCampbell
    Icon for Memorable Member rankMemorable Member
    7 years ago

    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! 

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        SteveCampbell
        Icon for Memorable Member rankMemorable Member
        7 years ago

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