Questions:
I needed to do a running DISTINCTCOUNT of of the number of customers (Source No_) from a transaction table (Value Entry) with 2 FILTERS (Document Type = 2 && Sales > 0)
That was fairly easy as below;
Active Customers = calculate(DISTINCTCOUNT(‘TABLE$Value Entry'[Source No_]), FILTER(T_Date, T_Date[Date] <= max(T_Date[Date])), FILTER(‘TABLE$Value Entry’,and(‘TABLE$Value Entry'[Document Type]=2, ‘TABLE$Value Entry'[Sales Amount (Actual)] > 0)))
And to get total customers (for percentage of Active Customers calculation, with the same filters) i did;
Total Active Customers = calculate(DISTINCTCOUNT(‘TABLE$Value Entry'[Source No_]), FILTER(T_Date, T_Date[Date] <= max(T_Date[Date])),FILTER(ALL(‘TABLE$Value Entry’),and(‘TABLE$Value Entry'[Document Type]=2, ‘TABLE$Value Entry'[Sales Amount (Actual)] > 0)))
Now this work fine as long as I am applying filters in Power BI Desktop from the same Table (Value Entry), like Business Category etc
But when I apply filters from another Table (‘Customer'[Source No_]) like sales person code or current Location code it only gives me a percentage of the TOTAL customers not the customers for that sales person code ot Current Location code.
Solutions
Add measure:
Active Customers = calculate(DISTINCTCOUNT(Records[Customer No]), FILTER(T_Date, T_Date[Date] <= max(T_Date[Date])), FILTER(Records,and(Records[Document Type]=2, Records[Sales Amount] > 0)))
Total Active Customers = calculate(DISTINCTCOUNT(Records[Customer No]), FILTER(T_Date, T_Date[Date] <= max(T_Date[Date])),FILTER(ALL(Records),and(Records[Document Type]=2, Records[Sales Amount] > 0)))
Total Active Customers By Customer = calculate(DISTINCTCOUNT(Customer[No]), FILTER(T_Date, T_Date[Date] <= max(T_Date[Date])),FILTER(ALL(Records),and(Records[Document Type]=2, Records[Sales Amount] > 0)))
Ref: https://community.powerbi.com/t5/Desktop/DISTINCTCOUNT-with-FILTERS-and-ALL/m-p/64811#M26794
Some additional samples
