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]) 

 

Image 1 showing total provision by customer (all customers ,filtered as per slicer) also another table where i have shown with manual filter applied to visual to understand how many customers contributing 80% (If we take cumulative total of 8 customers provision [Prov USD] it is comming upto 80% of the total provision amount)

 

My measure Customers Contribute 80% showing correct values 8 in the card in image 1,

 

Where as in image 2 i have done an additional filter (Slicer selected year 2021 data table) compared to image 1,

the distinct customer count is showing 8 instead of 7 ( if you check the 2nd table it is showing only 7 customers)

 

Image 2 

Measure 

Customers Contribute 80% =
   CALCULATE(
                 DISTINCTCOUNT(Data[Cr. Acc]),
                          FILTER('Customer DB',[Cumulative %]<=0.8))

 

 

Image 1 

 

Image 2

 

 

Regards,

Ansar.

 

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.

8 Replies

  • ansar's avatar
    ansar
    Helper II
    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))
       

       

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    Hi ansar 

     

    Can you share a [Cumulative %] measure code here?

     

    Is that year slicer connected to "Customer DB" or "Data" tables? Can you share samples of your tables here?

     

    Appreciate your Kudos✌️!!

     

    • ansar's avatar
      ansar
      Helper II
      4 years ago

      VahidDM : Thanks for your reply

      My fact table is connected to date table also with customer DB table, 
      Customer DB dont have direct relation ship with date table.

       

       

       

      Cumilative Prov =
      VAR CrNameProv = [Prov USD]
      RETURN
      IF(ISBLANK([Prov USD]),BLANK(),
      SUMX(
      FILTER(
      SUMMARIZE(ALLSELECTED(Data),'Customer DB'[Cr. Name],
      "Prov Amt", [Prov USD]),
      [Prov Amt] >= CrNameProv),
      [Prov Amt]))
       
      Prov USD RemoveCust Filter =
      IF(ISBLANK([Prov USD]),BLANK(),CALCULATE([Prov USD],ALLSELECTED('Customer DB')))

      1. Cumulative % = DIVIDE([Cumilative Prov],[Prov USD RemoveCust Filter])


      • v-yingjl's avatar
        v-yingjl
        Community Support
        4 years ago

        Hi ansar ,

        What is the [Prov USD] measure look like? Notice multiple measures quoted this measure

        In addition, you can consdier sharing a dummy sample file which just includes the Customer table, Data table and the Date table with the necessary columns in this issue.

         

        Best Regards,
        Community Support Team _ Yingjie Li