Combine Sum per Row

[AllanBerces]

Hi good day,

Can someone help me on my table, is there a way to sum the two area per row

  Asia Week1 + North Week 1 = Combine Week 1

DESIRED OUTPUT

Thank you

[Abhijeet_40]

It can be done using a pivot table.

This can also be done using a measure.

Total Remaining =

VAR CurrentArea = SELECTEDVALUE('Table'[Area])

VAR CurrentWeek = SELECTEDVALUE('Table'[Week])

RETURN

IF(

    CurrentArea = "Combine",

    CALCULATE(

        SUM('Table'[Remaining]),

        ALL('Table'),  -- Remove all filters

        'Table'[Area] IN { "Asia", "North" },

        'Table'[Week] = CurrentWeek

    ),

    SUM('Table'[Remaining])

)

[rajendraongole1]

Hi @AllanBerces - I have tried with measure as follows with one table input with above details. 

Actual Remaining Measure =

VAR WeekNum = SELECTEDVALUE(Input[Week No.])

RETURN

    CALCULATE(

        SUM(Input[Remaining]),

        Input[Area] IN {"Asia", "North"},

        Input[Week No.] = WeekNum

    )

can you please check this.

Hope it helps.

[AllanBerces]

Hi @rajendraongole1 thank you fo the reply but i need calculated column. I tried the one you provided in column but no luck.

[rajendraongole1 · Accepted solution]

Hi @AllanBerces - please check this

Actual Remaining =

IF(

    Input[Area] = "Combine",

    VAR WeekNum = Input[Week No.]

    RETURN

        SUMX(

            FILTER(

                Input,

                Input[Area] IN {"Asia", "North"} && Input[Week No.] = WeekNum

            ),

            Input[Remaining]

        ),

    Input[Remaining]

)

Hope this would help

[AllanBerces]

Hi @rajendraongole1 thank you very much for the reply, working perfectly

[rajendraongole1]

@AllanBerces -Happy to Help

Thank you

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Actual Remaining measure: = 
SWITCH (
    SELECTEDVALUE ( area[area] ),
    "Combine", CALCULATE ( SUM ( data[remaining] ), ALL ( area[area], area[sort_order] ) ),
    SUM ( data[remaining] )
)

[AllanBerces]

Hi @Jihwan_Kim thank you for the reply, I have only 1 table with Column Area, Week No. and Remaining.

[Jihwan_Kim]

Hi,

I suggest having a proper data model (star schema), but if you only have one table and cannot change the model, please try something like below.

Actual Remaining measure: = 
SWITCH (
    SELECTEDVALUE ( data[area] ),
    "combine", CALCULATE ( SUM ( data[remaining] ),  ALL ( data[area] ) ),
    SUM ( data[remaining] )
)