Dynamic Segmentation - with duplicate rows

[misul]

Hi, 

I am trying to follow this blog post about Dynamic Segmentation. https://www.daxpatterns.com/dynamic-segmentation/

I am stuck on one point - what to do when there are duplicate transactions? 

As you can see in the image, the category applied to Product A is not correct. 

Any help would be much appreciated. Thank you!

[Zubair_Muhammad]

HI @misul

Please Try this revision. Just summing the sales for product before determining category

Applied Category =
VAR Mysales =
    CALCULATE ( SUM ( Table1[Sales] ), ALLEXCEPT ( Table1, Table1[Product] ) )
RETURN
    CALCULATE (
        VALUES ( CategoryLookup[Description] ),
        FILTER (
            CategoryLookUp,
            MySales >= CategoryLookup[Lower]
                && Mysales < categoryLookup[Upper]
        )
    )

[misul]

Many thanks for the quick reply @Zubair_Muhammad. Now I can slice by the AppliedCategory and so it solves my first issue.

The next step for me is to "dynamically change" the applied category when any other slicer is applied (such as Year or Region).  

As you can see in the image below, when I select the year 2016, the applied categories are not correct anymore. I am trying to follow the link from DAX patterns but I am not sure I am going down the right road. 

 

[Zubair_Muhammad]

Hi @misul

Then you will need to use a MEASURE....becasue calculated columns are not dynamic

I have not tested it but try this MEASURE in the CategoryLookup Table

Measure =
CALCULATE (
    SUM ( Table1[Sales] ),
    FILTER (
        Table1,
        Table1[Sales] >= CategoryLookup[Lower]
            && Table1[Sales] < categoryLookup[Upper]
    )
)

[misul]

@Zubair_Muhammad You are right, I would need to use a measure... I tested the formula and gives the following error. 

But I have two follow-up questions : a) what is the idea behind creating a measure in the Lookup table instead of my Fact table?

b) Would it be possible to modify the initial formula for a calculated column "AppliedCategory" by asking it to sum up the sales values for a given year or region?

[Zubair_Muhammad]

Sorry I forgot it's a measure
Here is the revised measure


Measure =
CALCULATE (
SUM ( Table1[Sales] ),
FILTER (
Table1,
Table1[Sales] >= selectedvalue(CategoryLookup[Lower])
&& Table1[Sales] < selectedvalue(categoryLookup[Upper])
)
)

[Zubair_Muhammad]

I will get back to you on your questions

I am out of office now.. On my mobile

You can share your file with me as well if possible for you

[misul]

@Zubair_Muhammad

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) 

[misul]

EDIT: It seems that what I need is the ABC classification. Can anybody help to translate this to my simple table please?

https://www.daxpatterns.com/abc-classification-dynamic/

[misul]

@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?

[Zubair_Muhammad]

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

[misul]

@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]

HI @misul

Please see the attached file here

With your sample data

[Zubair_Muhammad · Accepted solution]

@misul

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] )
        )
    )

[misul]

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

[misul]

@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? 

[Zubair_Muhammad]

Hi @misul

To count the products,,,,you can try this MEASURE

CountProducts =
CALCULATE (
    DISTINCTCOUNT ( Table1[Product] ),
    FILTER ( ALL ( Table1[Product] ), [Measure] > 0 )
)

[Zubair_Muhammad]

@misul

Please see the revised file here

[misul]

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

[Zubair_Muhammad]

@misul

Also please see this article

https://www.daxpatterns.com/dynamic-segmentation/#dynamically-group-customers-by-sales-amount