Distinct count with filter showing wrong numbers

[ansar]

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.

[ansar · Accepted solution]

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.

[VahidDM]

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]

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

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

[Fowmy]

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

@Fowmy i added factable customer column it is showing now 11 count instead of 7

Count = COUNTROWS ( FILTER ( VALUES ( Data[Cr. Acc]),[Cumulative %]<=.80))

[ansar]

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

@ansar 

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