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,

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?