Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ansar
Helper II
Helper II

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

 

ansar_5-1632040428621.png

 

Image 1 

 

ansar_3-1632040365309.png

Image 2

ansar_4-1632040387191.png

 

ansar_6-1632043805120.png

 

Regards,

Ansar.

 

1 ACCEPTED SOLUTION
ansar
Helper II
Helper II

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

 

ansar_0-1632324950666.png

Regards,

Ansar.

View solution in original post

8 REPLIES 8
ansar
Helper II
Helper II

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

 

ansar_0-1632324950666.png

Regards,

Ansar.

VahidDM
Super User
Super User

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✌️!!

 

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

 

ansar_0-1632045640727.png

ansar_1-1632045684462.png

ansar_2-1632045801848.pngansar_3-1632045833802.png

ansar_4-1632045849523.png

 

 

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
Community Support
Community Support

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
Super User
Super User

@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 )
)
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@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_0-1632046366933.png

 

@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 ) )
ansar_0-1632045510885.png

 



 Regards,
Ansar.
 

@ansar 

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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.