Segment

[Anonymous]

Hi all,

I have the following dataset: 

Month	ID	Product	Amount
1	A-001	X	5
1	A-001	Y	4
1	A-002	X	1
1	A-003	X	10
2	A-001	X	1
2	A-001	Y	4
2	A-002	X	6
2	A-003	X	4

I would like to create customer segments based on amount, but as you see the segment changes every month. For example customer A-001 would be in month 1 in segment 5-10 but in month 2 in segment 1-5

I have tried to segment but it takes into account the total amount of all months (customer A-001, total amount 14, segment 10 - 15)

Could anyone help? 
Thanks a lot!

[Samarth_18]

Hi @Anonymous ,

You can create a segment table(SegmentRanges) based on your range. so based on your sample, I have created a sample table as below:-

Now create a measure as below:-

Segment =
VAR CurrentMonth =
    MAX ( SalesData[Month] )
VAR CurrentAmount =
    CALCULATE (
        SUM ( SalesData[Amount] ),
        FILTER (
            ALL ( Salesdata ),
            Salesdata[Month] = CurrentMonth
                && Salesdata[ID] = MAX ( Salesdata[ID] )
        )
    )
RETURN
    CALCULATE (
        MAX ( SegmentRanges[Segment] ),
        FILTER (
            SegmentRanges,
            CurrentAmount >= SegmentRanges[Lower Bound]
                && CurrentAmount <= SegmentRanges[Upper Bound]
        ),
        Salesdata[Month] = CurrentMonth
    )

output:-

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but please try something like below.

Please check the below picture and the attached pbix file.

Sales: = 
SUM( sales[Amount] )

Segment: = 
VAR _sales = [Sales:]
VAR _segment =
    FILTER ( Segment, Segment[min] <= _sales && Segment[max] >= _sales )
RETURN
    IF (
        HASONEVALUE ( 'ID'[ID] ) && HASONEVALUE ( 'Month'[Month] ),
        MAXX ( _segment, Segment[Category] )
    )