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