Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Segment

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 ...
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] )
    )
Samarth_18's avatar
Samarth_18
Icon for Community Champion rankCommunity 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:-