Need Help with DAX Formula to combine rows

[rizkus]

Hi All,

I am looking for help in regards to the data i have. currently i have this type of data

And then i have created a rank to categorize the order of the segment, as below

the outcome expected is a table, showing only the highest rank (in this case, segment B), but the Value Measure is added altogether:

I would also like to have it able to be filtered, so for example, if i filter segment A, it doesnt show any data anymore

Need your help and appreciate all the help, thanks!

[v-sgandrathi]

Hi @rizkus,

Thank you for your answer @Olufemi7

Checking in one last time to make sure all is good on your side. If there’s anything still outstanding or you need any further assistance, I’m here to help.

Thank you.

[Olufemi7]

Hello @rizkus 

Thanks for your question!
I have recreated your scenario and here’s the full explanation. 

DataTable:

ID Segment Value Measure

123	A	100
123	B	200
123	B	300

RankTable:

Segment Rank

B	1
A	2

TopSegmentTotal :=
IF (
    ISINSCOPE ( 'DataTable'[ID] ),
    VAR CurrentID =
        SELECTEDVALUE ( 'DataTable'[ID] )
    VAR TopRankForID =
        CALCULATE (
            MIN ( 'RankTable'[Rank] ),
            FILTER ( ALL ( 'DataTable' ), 'DataTable'[ID] = CurrentID )
        )
    VAR CurrentSegmentRank =
        MIN ( 'RankTable'[Rank] )
    VAR TotalForID =
        CALCULATE (
            SUM ( 'DataTable'[Value Measure] ),
            FILTER ( ALL ( 'DataTable' ), 'DataTable'[ID] = CurrentID )
        )
    RETURN
        IF (
            ISFILTERED ( 'DataTable'[Segment] )
                && SELECTEDVALUE ( 'DataTable'[Segment] ) = "A",
            TotalForID,
            IF ( CurrentSegmentRank = TopRankForID, TotalForID )
        )
)

Why the Difference

• In your screenshot, Segment B had only one row (300), so the total was 300.

• In my dataset, Segment B has two rows (200 + 300), so the total is 600.

• The measure is correct, it always sums all values for the same ID and shows the total only on the top‑ranked segment.

Filtering Behavior

• If you filter Segment B, the measure shows the total (600).

• If you filter Segment A, the measure shows blank (unless you allow it to return the total for A).

• If you want to show the raw row (300) instead of the total, use Value Measure directly and filter to that row.

Conclusion

• Use Value Measure if you want to show raw rows (200 and 300 separately, or just 300).

• Use TopSegmentTotal if you want to show the combined total (600) for the top segment.

• Both are correct , it depends on whether you want to display raw values or the calculated total.

• Slicers make it easy to toggle between segments and see how the measure behaves under filter

• Slicer showing TopSegment TotalValue Measure for Segment B only

[sanalytics]

@rizkus 

Apart from @DataNinja777  solution, Alternatively, you can use below tiny DAX code

Value Measure = 
VAR _Rank = 
RANKX( ALL( 'Table'[Segment]),CALCULATE( SUM( 'Table'[ValueMeasure])),,DESC)
VAR _Result = 
IF(
    _Rank =1,
    CALCULATE( SUM( 'Table'[ValueMeasure]), ALL( )
    ) ,BLANK()
)
RETURN
_Result

Below screenshots

when you select Segment A

Hopt it helps

Regards

sanalytics

[sayan_07]

Hi , @rizkus 

Try like this Please!!

First you need to do total measure value

Total Value = SUM(Data[Value])

Then find the Top Rank Value

Top Rank = MIN(Data[Rank])

Then show Total fo top Segments

Top Segment = IF(Data[Rank]=[Top Rank], [Total Value],BLANK())

[v-sgandrathi]

Hi @rizkus,

Just wanted to follow up and confirm that everything has been going well on this. Please let me know if there’s anything from our end.
Please feel free to reach out Microsoft fabric community forum.

Thankyou.

[v-sgandrathi]

Hi @rizkus,

Just wanted to follow up and confirm that everything has been going well on this. Please let me know if there’s anything from our end.
Please feel free to reach out Microsoft fabric community forum.

Thank you.

[v-sgandrathi]

Hi @rizkus,

Hi @op,

I reviewed your requirement and tested it using @DataNinja777  sample PBIX file. When I selected Segment A in the filter, the visual displayed blank values. This behavior is expected because the DAX measure is designed to return results only for the top ranked segment (Rank = 1) within each ID. In your dataset, Segment A does not hold the highest rank, so the condition in the formula . IF ( _currentSegment = _topSegment, _totalForID ) evaluates to blank for that segment. Essentially, the measure aggregates and displays values only for the segment with the top rank, while all other lower ranked segments show no results when filtered.

 Thank you and continue using Microsoft Fabric Community Forum
Have a nice day ahead.

[DataNinja777]

Hi @rizkus ,

You can produce your required output by writing a measure like below:

Combined Value = 
VAR _totalForID =
    CALCULATE (
        SUM ( Data[Value Measure] ),
        ALLEXCEPT ( Data, Data[ID] )
    )
VAR _topSegment =
    CALCULATE (
        SELECTEDVALUE ( SegmentRank[Segment] ),
        SegmentRank[Rank] = 1
    )
VAR _currentSegment =
    SELECTEDVALUE ( Data[Segment] )
RETURN
    IF (
        _currentSegment = _topSegment,
        _totalForID
    )

Then, you can put the measure along with the fields in the fact and dimension tables in a table visual as shown below:

I am attaching the pbix file for your reference.

[rizkus]

Hi,

thanks so much! quick question, if i filter the segment as "A", will it show?