Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Segment

Hi all,

I have the following dataset: 

MonthIDProduct Amount
1A-001X5
1A-001Y4
1A-002X1
1A-003X10
2A-001X1
2A-001Y4
2A-002X6
2A-003X4

 

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!

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

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

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    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] )
    )
  • Samarth_18's avatar
    Samarth_18
    Community Champion
    2 years ago

    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:-