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.

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