Pivot Table - Calculated Fields / Different From 1

[arvarr]

Hi
Needing some help with Pivot Table calculated fields or suggested solution.

Attached is a worksheet with data and pivot table.

I have entered the expected outcome required below the pivot table.
As i am not sure how to get the pivot to calculate what i want, I have hence put in formulas.

Ideally, I need to learn how to get the pivot table to calculate different scenarios.

Thanks,
arv

 

[arvarr]

Sample file as attached.

 

[combo]

At some level of complexity of the problem it is easier to switch to another excel tool (power query here). The source table is processed in power query to get previous month and previous year data. I did all the calculations in two queries and next use one as a source for pivot table.
There could be still some work to done, I hard-coded 2022 in query, dates could be used to have them in pivot table. Some of actions are pure recording outputs, some are written by me (as deducting two columns, however this can be done in three recorded steps: add new column, delete old one, rename new one).
The M-1 January data is different in my file, your formulas refer to Dec. 2022 mine to Dec. 2021.

combo

 

[arvarr]

Hi combo
Thanks for your reply and also providing the sample solution in the file.

I'm not familiar with the below. Do you mind letting me know what each of these steps are aiming to do?
The tSourced appears to be adding columns but not sure of the objective of the formula.

tSource
= Table.AddColumn(#"Added Custom2", "ID", each 12*([YearID]-2021)+[MonthID])
= Table.AddColumn(#"Added Custom3", "ID_01", each [ID]-1)
= Table.AddColumn(#"Added Custom4", "ID_12", each [ID]-12)

qOutput
= Table.ReplaceValue(#"Removed Columns", each [Qty_01], each [Qty] - [Qty_01],Replacer.ReplaceValue, {"Qty_01"})
= Table.ReplaceValue(Calc_01, each [Qty_12], each [Qty] - [Qty_12],Replacer.ReplaceValue, {"Qty_12"})

Thanks,
arv

 

[combo]

It's M language of power query, like in VBA, some actions can be recorded, but recorded code is only a part of the power of language.

Each #"Added Custom3" or Calc_01 (depending on formula name, if space inside, #"..." around are required) refers to prevoius step in query:
[tt]= Table.AddColumn(#"Added Custom2", "ID", each 12*([YearID]-2021)+[MonthID])[/tt] adds to previous step column "ID" with contents [YearID]-2021, here 0 or 1,
[tt]= Table.AddColumn(#"Added Custom3", "ID_01", each [ID]-1)[/tt] adds column "ID_01" with [ID]-1, i.e. previous month,
[tt]= Table.AddColumn(#"Added Custom4", "ID_12", each [ID]-12)[/tt] adds column "ID_12", ID of the same month year ago.
This is preprocessing in first query (tSource), those indexes will be used for linking to proper past months data.

qOutput query:
[tt]= Table.ReplaceValue(#"Removed Columns", each [Qty_01], each [Qty] - [Qty_01],Replacer.ReplaceValue, {"Qty_01"})[/tt]
[tt]= Table.ReplaceValue(Calc_01, each [Qty_12], each [Qty] - [Qty_12],Replacer.ReplaceValue, {"Qty_12"})[/tt]
respectively in steps "Removed Columns" and Calc_01, calculates M-M and Y-Y differences. It is an one line not recordable code. Instead, it is possible to (1) add new column and calculate difference, (2) delete unused column, and (3) rename column (not possible to have two Qty_01 columns) - three steps.

combo

 

[arvarr]

Hi Combo
Thanks for the explanation.

Say what about if Apples / Class A is in Jan-21 but not in Jan-22 (and vice-versa), the pivot table is not picking up anything.

Thanks,
arv

 

[arvarr]

HI Combo
When merging the tables, say if i have more columns in the data table, do i have to select all the columns from both tables?
Sorry as i am not very familiar with joining tables.

Thanks,
arv

 

[combo]

1.
You have exact dates in your table. A solution may be a custom column with date with day 1 and month and year from date. Alternatively index from month and year. In both solutions the original column with date should (in PQ) be deleted before grouping.
2.
When two tables are joined in power query, a column is added with records that match join criteria (fields and type of join), technically there is a table in each cell. A button near the new column name allows to expand these tables and select required field. In existing set of commands of pover query some entries have icons on the right, clicking them allows to customize the step, it affect data in next steps.


combo

 

[arvarr]

HI Combo
Thanks for the update.
Noted re point 2. I will give it a ahot.

Not 100% sure if i understand point 1 but I'll try.

Thanks,
arv

 

[arvarr]

Hi Combo
Re Point 1, i'll need some help again.

Added another row and call it Jackfruit/Class A with period Jan-22.
I intentionally omitted Jackfruit / Class A from period Jan-21.

With the merge, it will not be able to match the record (Jackfruit/Class A).

What's the best way to overcome this?

Thanks,
arv

 

[combo]

After merging tables with past (M-1, M-12') data and expanding columns, Qty_01 and Qty_12 have nulls, they have to be replaced by 0 for future calculations, otherwise nulls are returned. I added a step 'Replaced Value' after 'Removed Columns' in 'qOutput' and qOutput (2)' queries:
[tt]= Table.ReplaceValue(#"Removed Columns",null,0,Replacer.ReplaceValue,{"Qty_01", "Qty_12"})[/tt]
(a result from recorder after selecting 'Removed columns' step, selecting fields 'Qty_01' and 'Qty_12', and "replace values' action from menu - replace 'null' by '0')

The full code (advanced editor):
[pre]let
Source = tSource,
#"Filtered Rows" = Table.SelectRows(Source, each ([YearID] = 2022)),
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Product", "Class", "ID_01"}, tSource, {"Product", "Class", "ID"}, "tSource", JoinKind.LeftOuter),
#"Expanded {0}" = Table.ExpandTableColumn(#"Merged Queries", "tSource", {"Qty"}, {"Qty_01"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded {0}", {"Product", "Class", "ID_12"}, tSource, {"Product", "Class", "ID"}, "tSource", JoinKind.LeftOuter),
#"Expanded {0}1" = Table.ExpandTableColumn(#"Merged Queries1", "tSource", {"Qty"}, {"Qty_12"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded {0}1",{"YearID", "ID", "ID_01", "ID_12"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,0,Replacer.ReplaceValue,{"Qty_01", "Qty_12"}),
Calc_01 = Table.ReplaceValue(#"Replaced Value", each [Qty_01], each [Qty] - [Qty_01],Replacer.ReplaceValue, {"Qty_01"}),
Calc_12 = Table.ReplaceValue(Calc_01, each [Qty_12], each [Qty] - [Qty_12],Replacer.ReplaceValue, {"Qty_12"})
in
Calc_12[/pre]

Note that:
- excel may automatically change connection name when the query changes,
- pivot table duplicates query and uses the new connection, su it is necessary to change the query for pivot table too,
- power query has a simple pivot functionality, you can consider building output tables in power query and return results in tables (in a copy of qOutput, a step (Removed Columns1) [tt]= Table.RemoveColumns(Calc_12,{"Qty", "MonthID", "Qty_12"})[/tt] and next (Pivoted Column) [tt]= Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Month]), "Month", "Qty_01", List.Sum)[/tt] loaded to worksheet returns a table close to pivot table summary for 'Qty_01').

combo

 

[arvarr]

Hi Combo
The pivot table lists all the fruits that is listed in 2022.
However, if there is a mismatch whereby a type of fruit that is in 2021 but not in 2022, the pivot table will not list the item.

Thanks,
arv

 

[combo]

You may consider to rebuild queries for more robust structure:
1. create a list 1 .. 12 of months to have all months even if some data is missing, a query with:
[pre]let
Source = {1..12},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"[/pre]
The same for {"A" .. "B"} list
2. have a full list of fruits, remove duplicates in query, from single column table: [tt]= Table.Distinct( )[/tt]
3. combine tables, to have full set of row/column headers,
4. merge M-1 and M-12 data.

To handle nulls, you can either replace 'null' by 0 or add conditional column.

Welcome to Power Query world. When you experiment, frequently save the query and workbook, sometimes wrong query can hang or close excel.

combo