Forum Discussion

MRB's avatar
MRB
Regular Visitor
8 years ago
Solved

Distinct Counts within dynamic group.

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-200004999
5 to 10K50009999
10 to 25K1000024999
25 to 50K2500049999
50 to 100K5000099999
100 to 250K100000249999
250 to 500K250000499999
500 to 750K500000749999
750K to 1M750000999999
1 Million +100000099999999

 

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. 

Need Help
  • MRB's avatar
    MRB
    8 years ago

    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 GROUPBASE SALE GROUPINGS PRODUCT SALES%GT BASE SALE GROUPINGS PRODUCT SALESBASE SALE GROUPINGS PRODUCT SALES COUNTS
    No Sales($3,222)0.00%24743
    1 to 5K$5,344,7407.12%4327
    5 to 10K$5,031,5446.70%699
    10 to 25K$11,600,31715.45%719
    25 to 50K$13,966,19618.61%395
    50 to 100K$15,599,33820.78%223
    100 to 250K$18,208,89824.26%121
    250 to 500K$3,586,6344.78%10
    500 to 750K$526,3880.70%1
    750K +$1,203,4621.60%1
        
     $75,064,295100%31239

2 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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's avatar
      MRB
      Regular Visitor
      8 years ago

      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 GROUPBASE SALE GROUPINGS PRODUCT SALES%GT BASE SALE GROUPINGS PRODUCT SALESBASE SALE GROUPINGS PRODUCT SALES COUNTS
      No Sales($3,222)0.00%24743
      1 to 5K$5,344,7407.12%4327
      5 to 10K$5,031,5446.70%699
      10 to 25K$11,600,31715.45%719
      25 to 50K$13,966,19618.61%395
      50 to 100K$15,599,33820.78%223
      100 to 250K$18,208,89824.26%121
      250 to 500K$3,586,6344.78%10
      500 to 750K$526,3880.70%1
      750K +$1,203,4621.60%1
          
       $75,064,295100%31239