Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
1 year ago
Solved

Combine Sum per Row

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

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    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

9 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    1 year ago

    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's avatar
      AllanBerces
      Post Prodigy
      1 year ago

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

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        1 year ago

        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] )
)
  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    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's avatar
      AllanBerces
      Post Prodigy
      1 year ago

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

      • rajendraongole1's avatar
        rajendraongole1
        Super User
        1 year ago

        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

  • Abhijeet_40's avatar
    Abhijeet_40
    Helper I
    1 year ago

    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])
    )