Group By Columns - P&L

[awitt]

I am trying to group multiple columns into one of two categories with the intention of building a financial statement. I have 10 or so different revenue columns and really one COGS column. I don't want to sum all of these columns together so the GroupBy function in PowerQuery doesn't seem to be appropriate. 

Each row in the dataset is a different line of a customer order which with its own set of unique characteristics. Basically i'm looking for a quick way to identify all of the revnue columns together so they can be put into a drilled down matrix.

Ex.

Revenue

      SumOfRevColumn1

      SumOfRevColumn2

      SumOfRevColumn3

Expenses

      SumofCogsColumn

Margin

     Calculated Margin Column

[v-xicai]

Hi  @awitt ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai]

Hi,

You can create group columns like formula below, then create new hierarchy ,and let the other sub columns add to the new hierarchy Group1.

Group1 = "Group1"

          

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.