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] ) ) )
EDIT: It seems that what I need is the ABC classification. Can anybody help to translate this to my simple table please?
Zubair_Muhammad Just wondering if you could help me out with my first question..
a) what is the idea behind creating a measure in the Lookup table instead of my Fact table? How can this apply the dynamic category when slicing per year and per region?
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 ) )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
Also please see this article
https://www.daxpatterns.com/dynamic-segmentation/#dynamically-group-customers-by-sales-amount
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 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