Forum Discussion

Copper Contributor

Jun 21, 2023

Pivot Table group positive and negative data

As you can see in the below image i have created two separate Pivot tables to group the positive and negative values separately. 1. Is there a way to group positive and negative value as sub groups ...

Lorenzo
Jun 28, 2023
Rohan110

Attached an example with a single [Amount] column with Positive & Negative values:

Edit the query in the attachment and follow the steps in APPLIED STEPS (all done with the UI). Once you've separated your [Income] & [Expense] (the added Custom steps) and removed [Amount] that's no longer necessary, right-click on column [Category] and in the drop-down menu select Unpivot Other Columns

IMPORTANT:
- For [Expense] you'll see I used function Number.Abs to convert to negative [Amount]s to positive. If you want your Expense to show negative in your PivotTable then remove Number.Abs (you will have to adjust the Graph DAX measure)
- When you add Custom Columns [Income] & [Expense] Power Query doesn't auto. Type the columns. Taking ex. [Income] when you create it, it displays exactly as below in PQ formula bar:
You must edit the formula and add, before ): , type number then Validate:
You must do the same for the [Expense] column
Reason is: default data Type in Power Query is Type any. Column(s) of Type any loaded to the Excel Data Model will be assigned the Type Text (no Sum, Avg... possible)

PQ_SplitIncomeExpense.xlsx101 KB

Lorenzo

Silver Contributor

Rohan110

Attached an example with a single [Amount] column with Positive & Negative values:

Edit the query in the attachment and follow the steps in APPLIED STEPS (all done with the UI). Once you've separated your [Income] & [Expense] (the added Custom steps) and removed [Amount] that's no longer necessary, right-click on column [Category] and in the drop-down menu select Unpivot Other Columns

IMPORTANT:

- For [Expense] you'll see I used function Number.Abs to convert to negative [Amount]s to positive. If you want your Expense to show negative in your PivotTable then remove Number.Abs (you will have to adjust the Graph DAX measure)

- When you add Custom Columns [Income] & [Expense] Power Query doesn't auto. Type the columns. Taking ex. [Income] when you create it, it displays exactly as below in PQ formula bar:

You must edit the formula and add, before ): , type number then Validate:

You must do the same for the [Expense] column

Reason is: default data Type in Power Query is Type any. Column(s) of Type any loaded to the Excel Data Model will be assigned the Type Text (no Sum, Avg... possible)

PQ_SplitIncomeExpense.xlsx101 KB

Rohan110

Copper Contributor

Oct 03, 2023

Lorenzo

There is a problem with this approach. As you can see, with this approach, as transfers has both positive and negative values in the data, it gets listed in both income and expense. With this approach I'm unable to calculate my net income and net expenses. Could you please help with this?

peiyezhu
Bronze Contributor
Oct 03, 2023
can you upload your data and expected result?

Lorenzo

Silver Contributor

Oct 03, 2023

Rohan110

Note quite sure this will do exactly what you expect....: Aggregate the amounts for the Transfers category. Then, if the aggregated amount is >= 0 Transfers will be reported as Income otherwise as Expense

let
    Source = Excel.CurrentWorkbook(){[Name="SourceTable"]}[Content],
    ChangedTypes = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Amount", type number}}),
    Transfers = Table.Group(
        Table.SelectRows(ChangedTypes, each ([Category] = "Transfers")),
        {"Category"}, {{"Amount", each List.Sum([Amount]), type nullable number}}
    ),
    OtherCategories = Table.SelectRows(ChangedTypes, each ([Category] <> "Transfers")),
    Combined = Table.Combine({OtherCategories, Transfers}),
    AddedIncome = Table.AddColumn(Combined, "Income", each
        if [Amount] >= 0 then [Amount] else null, type number
    ),
    AddedExpense = Table.AddColumn(AddedIncome, "Expense", each
        if [Amount] < 0 then Number.Abs([Amount]) else null, type number
    ),
    RemovedAmount = Table.RemoveColumns(AddedExpense,{"Amount"}),
    UnpivotedColumns = Table.UnpivotOtherColumns(RemovedAmount, {"Category"}, "Attribute", "Value")
in
    UnpivotedColumns

PQ_SplitIncomeExpense.xlsx24 KB