Forum Discussion

hamadani's avatar
hamadani
Frequent Visitor
4 years ago
Solved

Comparing count values with and without filters

Hi All,

 

I am trying to compare the total count difference of policies per company with AND without the slicer.

 

Here is a simple example of my scenario:

 

Compnay NamePolicy NumberFilter XFilter Y
Company A123TrueFalse
Company A456FalseFalse
Company A789FalseTrue
Company A123FalseFalse
Company A050TrueTrue

 

Therefore, If Slicer X is set to True and slicer Y is set to False, I expect to get 25% for Company A (1 distinct policy which satisfies the filters divided by total of 4 distinct policies for company A). The point here is that the denomitor should be always constant regardless of filter values.

 

I used the below DAX but this will change the denomitor as I apply the filters:
PoliciesPerCompany = CALCULATE(DISTINCTCOUNT(MyTable[PolicyNumber]), GROUPBY(MyTable,MyTable[CompanyName]))
 
Any help would be appreciated.
Need Help
  • ArmandoFranco's avatar
    ArmandoFranco
    4 years ago

    Policies by company = DIVIDE( DISTINCTCOUNT(MyTable[PolicyNumber]) , CALCULATE( DISTINCTCOUNT(MyTable[PolicyNumber]), ALL(MyTable[Filter X]), ALL(MyTable[Filter Y])))

    The numerator applies the whole filter context, the denominator replaces Filter X and Filter Y with all the values for both.

1 Reply

  • ArmandoFranco's avatar
    ArmandoFranco
    Frequent Visitor
    4 years ago

    Policies by company = DIVIDE( DISTINCTCOUNT(MyTable[PolicyNumber]) , CALCULATE( DISTINCTCOUNT(MyTable[PolicyNumber]), ALL(MyTable[Filter X]), ALL(MyTable[Filter Y])))

    The numerator applies the whole filter context, the denominator replaces Filter X and Filter Y with all the values for both.