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

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