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] ) ) )
Much appreciated. Can't upload my source files, but I have posted sample fact and lookup tables in images above.
Would be great to have any info on how to achieve this dynamic slicing .. with or without using the following methods.
Topics I have researched:
a. Dynamic documentation ( https://www.daxpatterns.com/dynamic-segmentation/) - My desired solution seems to match this, but there are no duplicate rows in their sample file, so I couldn't follow this.
b. SUMMARISE ROLLGROUP function -https://curbal.com/blog/glossary/summarize-dax ( still figuring out how to use this function)
EDIT: It seems that what I need is the ABC classification. Can anybody help to translate this to my simple table please?
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 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?
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