Grouping on two columns according to values sign

[Anonymous]

Hello,

I was wondering if it is possible to do this kind of grouping for two columns based on the sign of the interval values (and how).

The months are selected by a slicer in the report.

Thanks for the help

[Anonymous]

Hello @tamerj1 & @Jihwan_Kim , thank you for your nice answsers.

@tamerj1's solution is the closest to what I need. I'll just add a new measure so that the period label reflects more precisely the selected period and hid the month group.

Thank you again for the help and the effort, much appreciated

[tamerj1 · Accepted solution]

Hi @Anonymous 
Please refer to the attached sample file with the solution.

Rank = 
VAR Ranking1 =
    RANKX ( FILTER ( 'Table', 'Table'[Value] > 0 ), 'Table'[Month],, ASC, Dense )
VAR Ranking2 =
    RANKX ( FILTER ( 'Table', 'Table'[Value] < 0 ), 'Table'[Month],, ASC, Dense )
VAR Result =
   IF ( 'Table'[Value] > 0, Ranking2, Ranking1 )
RETURN
    Result

Group = 
VAR CurrentGroupTable = 
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Rank] ) )
VAR Starting = 
    MINX ( CurrentGroupTable, 'Table'[Month] )
VAR Ending = 
    MAXX ( CurrentGroupTable, 'Table'[Month] )
RETURN
    FORMAT ( Starting, "MMMM" ) & " - " & FORMAT ( Ending, "MMMM" )

Average Value = AVERAGE ( 'Table'[Value] )

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

New Table = 
VAR _previousvalue =
    ADDCOLUMNS (
        Data,
        "@prev",
            CALCULATE (
                SUM ( Data[Value] ),
                FILTER ( Data, Data[Month number] = EARLIER ( Data[Month number] ) - 1 )
            )
    )
VAR _condition =
    ADDCOLUMNS (
        _previousvalue,
        "@condition",
            IF ( Data[Value] * [@prev] >= 0, 0, 1 )
    )
VAR _group =
    ADDCOLUMNS (
        _condition,
        "@group",
            SUMX (
                FILTER ( _condition, Data[Month number] <= EARLIER ( Data[Month number] ) ),
                [@condition]
            )
    )
VAR _newtable =
    ADDCOLUMNS (
        _group,
        "@month",
            MAXX (
                FILTER (
                    Data,
                    Data[Month number]
                        = MINX ( FILTER ( _group, [@group] = EARLIER ( [@group] ) ), Data[Month number] )
                ),
                Data[Month]
            ) & "~"
                & MAXX (
                    FILTER (
                        Data,
                        Data[Month number]
                            = MAXX ( FILTER ( _group, [@group] = EARLIER ( [@group] ) ), Data[Month number] )
                    ),
                    Data[Month]
                )
    )
VAR _avgcolumn =
    ADDCOLUMNS (
        _newtable,
        "@avgvalue", AVERAGEX ( FILTER ( _group, [@group] = EARLIER ( [@group] ) ), Data[Value] )
    )
RETURN
    SUMMARIZE ( _avgcolumn, [@month], [@avgvalue] )