Pivot Table group positive and negative data

[Rohan110]

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 in a single Pivot table instead of two like i have done.

2. Can i use the grand total of positive values and then divide each negative values by the income grand total to get the percentage of the expenses ie. (Each expense)/(Income grand total)*100

This way i can calculate what percentage of my income makes up each of my expenses. 

[#2]

@Rohan110 

When you find a minute, When Array table is updated/ sorted, the adjacent column cells aren't aligned/linked is pending your feedback...

Re. your Pivot Table question the following is doable (there's a blank row between Categories for clarity but it can be removed) but not with a classic Pivot Table only, this is a single Pivot though:

You tagged your thread with Excel, Excel on Mac, Excel on mobile, Power BI. The above is acheivable with Excel >= 2013 on Windows and Power BI but not on Excel on Mac. Interested?

[#3]

Hi Lz.,
I use Microsoft office 365, with the latest excel version. Please provide your suggestion.

[#4]

Hi @Rohan110 

Sample attached:

#1 Table1 is loaded to Power Query to Unpivot [Expense] & [Income] (kept the default "Attribute" column name - easy to change)

#2 Unpivoted table loaded to the Excel Data Model (Windows only)

#3 A couple of DAX measures (don't ask too much I have very basic knowledge)

[#5]

Hi L z.
Thanks for your quick response. Could you please let me know how you carried out unpivoting step which resulted in categorizing the data as rows and columns.
Also, my raw data has a single column of income and expenses, income in positive values and expense as negative values. Does this mean i need to split them into separate column, positive and negative and then carryout your method? Appreciate your support on this.

[#6 · Accepted solution]

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

[#7]

Hi @Rohan110 

Does no news means good news? (Marking solutions helps people who Search...)

[#8]

Hi Lz,
Thanks for the useful tip and also taking time in explaining it in very detailed format.

[#9]

@Lorenzo 

Simpler step to categorize the data. But thanks. This helped me to segregate into two different categories. 

[#10]

@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? 

[#11]

can you upload your data and expected result?

[#12]

@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