Forum Discussion
Dynamic Segmentation - with duplicate rows
I have used this MEASURE in the LookUp Table
Measure = VAR mysales = CALCULATE ( SUM ( Table1[Sales] ) ) RETURN CALCULATE ( SUM ( Table1[Sales] ), FILTER ( VALUES ( Table1[Product] ), mysales >= SELECTEDVALUE ( CategoryLookup[Lower] ) && mysales < SELECTEDVALUE ( categoryLookup[Upper] ) ) )
HI misul
Sorry for late reply.
Actually this way MEASURE will return values/sales amount which can be shown in VALUES section of TABLE or MATRIX visualization
If you create a MEASURE to compute categories in the fact TABLE..you cannot put them in TABLE rows
If you could copy paste some sample data from Excel.... I will try to work it out for you by creating a small pbix file
Zubair_Muhammad I also found that link, that's how I knew my problem is called "dynamic segmentation". I will try to follow the link again, but couldn't translate it to my solution.
Here are my tables.
Table 1
| Year | Product | Sales | Region |
| 2016 | A | 5 | North |
| 2016 | A | 5 | North |
| 2016 | A | 10 | South |
| 2017 | A | 10 | East |
| 2016 | B | 3 | East |
| 2017 | B | 2000 | South |
| 2016 | C | 5 | West |
| 2016 | C | 5 | North |
| 2016 | D | 23 | West |
| 2017 | D | 1 | South |
| 2016 | E | 2 | North |
| 2016 | E | 1 | South |
| 2017 | E | 50 | West |
| 2017 | F | 13 | East |
CategoryLookup Table
| Lower | Upper | Description | SortOrder |
| 0 | 14 | <15 | 1 |
| 15 | 99 | 15-100 | 2 |
| 100 | 499 | 100-500 | 3 |
| 500 | 9999 | >500 | 4 |
I have used this MEASURE in the LookUp Table
Measure = VAR mysales = CALCULATE ( SUM ( Table1[Sales] ) ) RETURN CALCULATE ( SUM ( Table1[Sales] ), FILTER ( VALUES ( Table1[Product] ), mysales >= SELECTEDVALUE ( CategoryLookup[Lower] ) && mysales < SELECTEDVALUE ( categoryLookup[Upper] ) ) )Hi misul
To count the products,,,,you can try this MEASURE
CountProducts = CALCULATE ( DISTINCTCOUNT ( Table1[Product] ), FILTER ( ALL ( Table1[Product] ), [Measure] > 0 ) )Zubair_Muhammad Many thanks!!!!!! This seems to work.
I will apply it to my data (which has more rows and more granularity) ... and hopefully it will still work.
Zubair_Muhammad I celebrated too early. The current setup doesn't allow me to count the number of rows in each category, which is my final goal.
for example: How many products with Sales <15 in 2017 in Region North?
It solves my issue. Thanks a million! Zubair_Muhammad
For reference, this post shows how to:
a. Categorise your data into buckets of 0-15, 15-100 etc
b. Distinct count those categories