Forum Discussion

Rajesh_Konda's avatar
Rajesh_Konda
Frequent Visitor
4 years ago

Sum column values based on other Text column

Hi All,

 

Need some help on power query basically i have 2 columns one is text column partition and other is total Hours. Partition key has different values like dcs, csm, component... Now i would like to sum total hours based on text column value.

ex : partition        totalHours

       DCS                   2hrs

       component        5hrs ...

 

can any one please help me out on this. thanks

 

Need Help

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    Rajesh_Konda Just put PartitionKey in a table visualization along with a default SUM of TotalOfHours, or a measure like: Measure = SUM('Table'[TotalOfHours])

     

    ?

    • visheshjain's avatar
      visheshjain
      Impactful Individual
      4 years ago

      Hi Rajesh_Konda,

       

      Try converting the data type of TotalHours column to duration.
      It will make life much simpler for you.

       

      Thanks,

      Vishesh Jain

  • Rajesh_Konda's avatar
    Rajesh_Konda
    Frequent Visitor
    4 years ago

    Thanks Greg_Deckler for quick response, Yes it worked.

    but i forgot to mention each column value has to be multipled with different number on total hours. can this be achievable?

    Ex  DCS *3, component*5

    partition        totalHours      GrandTotal

           DCS                   2hrs         6hrs

           component        5hrs ...     25hrs 

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      4 years ago

      Rajesh_Konda In that case, you could do something like:

      Measure =
  VAR __Sum = SUM('Table'[TotalOfHours])
  VAR __Multiple = 
    SWITCH(MAX('Table'[PartitionKey]),
      "DCS",3,
      "component",5
    )
RETURN
  __Sum * __Multiple