Forum Discussion

ansar's avatar
ansar
Helper II
4 years ago
Solved

Distinct count with filter showing wrong numbers

Hi, Can any one help here what is the correct DAX formula ,    I have a measure to find out unique numbers of customers who is contributing 80% of the provision value (Measure [Prov USD])    Ima...
Need Help
  • ansar's avatar
    ansar
    4 years ago

    Thanks Fowmy VahidDM v-yingjl 

     

    It got resolved through FILTER functions.

     

    Customers Contribute 80% =
    CALCULATE(
    DISTINCTCOUNT(Data[Cr. Acc]),
    FILTER(ADDCOLUMNS(FILTER('Customer DB',[Cumulative Prov %]),"**bleep** %",[Cumulative Prov %]),[Cumulative Prov %]<=.8))

     

    Regards,

    Ansar.

Fowmy's avatar
Fowmy
Super User
4 years ago

ansar 

Can you try the following measure? You can add the Customer Key or Code .

Customers Contribute 80% =
COUNTROWS (
    FILTER ( VALUES ( 'Customer DB'[Customer ID] ), [Cumulative %] <= 0.8 )
)
  • ansar's avatar
    ansar
    Helper II
    4 years ago
    Fowmy  Thanks for your swift reply !!
    I have created below measure and it is showing 33K instead of 7
    Count = COUNTROWS ( FILTER ( VALUES ( 'Customer DB'[Cr. Name]), [Cumulative %] <= 0.8 ) )

     



     Regards,
    Ansar.
     
    • Fowmy's avatar
      Fowmy
      Super User
      4 years ago

      ansar 

      If you can share a dummy file with your scenario and show the issue then I can check on that.

  • ansar's avatar
    ansar
    Helper II
    4 years ago
    Fowmy i added factable customer column it is showing now 11 count instead of 7
    Count = COUNTROWS ( FILTER ( VALUES ( Data[Cr. Acc]),[Cumulative %]<=.80))