Forum Discussion

Chanleakna123's avatar
Chanleakna123
Post Prodigy
7 years ago
Solved

Sum Based on Group

hi , 

I have no clue how to Sum each column based on Group, Total Work Hours in MN is where i calculated on Excel Column , 

For Hualage Trucks : Work Hours in MN + Stop Hours in MN 

For Key Account trucks : Idling Hours in MN + Work Hours in MN 

 

How to acheive this on Power BI ? 

 

Groups (groups)Idling Hours in MNWork Hours in MNStop Hours in MNTotal Work Hours In MN
Haulage Trucks30772116888
Haulage Trucks3211491821331
Key Account Trucks53189715242
Key Account Trucks1364061008542
Need Help
  • d_gosbell's avatar
    d_gosbell
    7 years ago

    You could do this by using SUMX to generate a row context, then testing for the value of the Group using an IF or SWITCH statement.

     

    So something like the following should work:

     

    SUMX( table1 , 
    SWITCH( table1[Groups (groups)]
      , "Haulage Trucks", table1[Work Hours in MN] + table1[Stop Hours in MN] 
      , "Key Accounts Trucks",  table1[Idling Hours in MN] + table1[Work Hours in MN] 
      )
    )

1 Reply

  • d_gosbell's avatar
    d_gosbell
    Super User
    7 years ago

    You could do this by using SUMX to generate a row context, then testing for the value of the Group using an IF or SWITCH statement.

     

    So something like the following should work:

     

    SUMX( table1 , 
    SWITCH( table1[Groups (groups)]
      , "Haulage Trucks", table1[Work Hours in MN] + table1[Stop Hours in MN] 
      , "Key Accounts Trucks",  table1[Idling Hours in MN] + table1[Work Hours in MN] 
      )
    )