Distinct Counts within dynamic group.

[MRB]

Hellow Bi - Community,    
     I am trying to get a count of distinct count of my customer from a virtual / dynamic group.  Basically, I'm grouping and then summing all my sales in 10 sales amount buckets over a period of time (running 12 months ) using the below formula and data table.   

BASE SALE GROUPINGS RUNNING 12 Months =
CALCULATE([BASE SALES RUNNING 12 MONTHS],
FILTER(VALUES('SLP05 - CUSTOMER_INFO'[CUSTOMER_KEY]),
COUNTROWS(
FILTER( 'BASE SALES GROUPINGS',
[BASE SALE] > 'BASE SALES GROUPINGS'[MIN] &&
[BASE SALE] < 'BASE SALES GROUPINGS'[MAX] )) > 0 ))  

'BASE SALES GROUPINGS'   

BASE SALES GROUP | MIN | MAX

<5K	-20000	4999
5 to 10K	5000	9999
10 to 25K	10000	24999
25 to 50K	25000	49999
50 to 100K	50000	99999
100 to 250K	100000	249999
250 to 500K	250000	499999
500 to 750K	500000	749999
750K to 1M	750000	999999
1 Million +	1000000	99999999

With the following results in a simple table -  and the results being accurate.   

My Question is -  how do I rewrite / new version of my 'BASE SALE GROUPINGS RUNNING 12 Months'  measure to count distinct customers i.e. '' SLP05 - CUSTOMER_INFO'[CUSTOMER_KEY] " across the different sales amount buckets instead of the totals for each sales group.     Essentially -  I need know, out my 5000 customers how many are in each group. 

[v-jiascu-msft]

Hi @MRB,

Can you share a dummy sample? It seems both [BASE SALES RUNNING 12 MONTHS] and [BASE SALE] are measures. Can this formula work?

BASE SALE GROUPINGS RUNNING 12 Months NEW =
CALCULATE (
    DISTINCTCOUNT ( 'SLP05 - CUSTOMER_INFO'[CUSTOMER_KEY] ),
    FILTER (
        VALUES ( 'SLP05 - CUSTOMER_INFO'[CUSTOMER_KEY] ),
        COUNTROWS (
            FILTER (
                'BASE SALES GROUPINGS',
                [BASE SALE] > 'BASE SALES GROUPINGS'[MIN]
                    && [BASE SALE] < 'BASE SALES GROUPINGS'[MAX]
            )
        )
            > 0
    )
)

Best Regards,

Dale

[MRB · Accepted solution]

Dale -  Thanks your measure worked -   noted - I changed up some of the summing measures a bit so amounts don't match my initial request examples but the results are what I was looking for. 

BASE SALES GROUP	BASE SALE GROUPINGS PRODUCT SALES	%GT BASE SALE GROUPINGS PRODUCT SALES	BASE SALE GROUPINGS PRODUCT SALES COUNTS
No Sales	($3,222)	0.00%	24743
1 to 5K	$5,344,740	7.12%	4327
5 to 10K	$5,031,544	6.70%	699
10 to 25K	$11,600,317	15.45%	719
25 to 50K	$13,966,196	18.61%	395
50 to 100K	$15,599,338	20.78%	223
100 to 250K	$18,208,898	24.26%	121
250 to 500K	$3,586,634	4.78%	10
500 to 750K	$526,388	0.70%	1
750K +	$1,203,462	1.60%	1
	$75,064,295	100%	31239