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 ...
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
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