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.

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