Reformatting Margin Data with calculations

[snuffnchess]

I have put together a very small sampling of test data to show what I am trying to accomplish, but am not 100% of how to go about putting this in action.

We have a list of owners that pay a certain percent of margin of sales as a royalty payment. Which if it were just left at that it would be simple. There are then some exceptions where the percentages change based on the customer they are for etc. Then there are certain owners that we look back at a 3 week history for, and adjust numbers if the numbers have changed.

From the Fixed Data tab of the workbook, it will have the percentages, the exception accounts, minimum fees (if for some reason the minimum is not hit), and then column F to reflect if we are looking at the data from 3 weeks ago.

Current Tab has the margin in $ on it from current export and the margin for the exception accounts
3 wk original tab has the margin in $ as it appeared 3 weeks ago on reports
3 wk updated tab has the margin in $ as it appears today when looking at info from 3 weeks ago (there are sometimes adjustments that come in over time)

Desired result is ultimately how we want the data to look for the end result. (without the colors - i added those in just so that it was more obvious where the calculations came from).

Where do I even begin with this?


Fixed Data

Start.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Owner	Item 1	Item 3	Item 2	Item 4	3wk Comparison	Minimum	Min Waive	Alt Fee	Item 3 Exception % 1	Item 3 Exception % 2	Item 1 Exception Name 1	Item 1 Exception % 1	Item 1 Exception Name 2	Item 1 Exception % 2	Item 1 Exception Name 3	Item 1 Exception % 3	Notes
2	Group 1	18%	24%	15%	5%	n	$ 250.00
3	Group 2	18%	24%	15%	5%	y	$ 250.00
4	Group 3	17%	20%	17%	5%	y			$ 125.00	10%	10%
Fixed Data

Current

Start.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Owner	Item 1 Margin	Item 2 Margin	Item 3 Margin	Item 4 Margin	Item 3 Exception Name 1	Item 3 Exception Margin 1	Item 3 Exception Name 2	Item 3 Exception Margin 2	Item 1 Exception Name 1	Item 1 Exception Margin 1	Item 1 Exception Name 2	Item 1 Exception Margin 2	Item 1 Exception Name 3	Item 1 Exception Margin 3	Notes
2	Group 1	$ 144.03	$ 175.40	$ -	$ -
3	Group 2	$ 684.55	$ 41.02	$ -	$ -
4	Group 3	$ 12,273.59	$ 4,186.31	$ 3,028.64	$ -	Company 11396624	$ 1,468.17	Company 11255381	$ 190.38
Current

3 wk Original

Start.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Owner	Item 1 Margin	Item 2 Margin	Item 3 Margin	Item 4 Margin	Item 3 Exception Name 1	Item 3 Exception Margin 1	Item 3 Exception Name 2	Item 3 Exception Margin 2	Item 1 Exception Name 1	Item 1 Exception Margin 1	Item 1 Exception Name 2	Item 1 Exception Margin 2	Item 1 Exception Name 3	Item 1 Exception Margin 3
2	Group 1	$ 156.92	$ 375.42	$ 5.67	$ -
3	Group 2	$ 34.01	$ 91.93	$ -	$ -
4	Group 3	$ 11,783.00	$ 4,019.90	$ 2,907.50	$ -	Company 11396624	$ 1,409.40	Company 11255381	$ 182.76
3 Wk Original

3 wk Updated

Start.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Owner	Item 1 Margin	Item 2 Margin	Item 3 Margin	Item 4 Margin	Item 3 Exception Name 1	Item 3 Exception Margin 1	Item 3 Exception Name 2	Item 3 Exception Margin 2	Item 1 Exception Name 1	Item 1 Exception Margin 1	Item 1 Exception Name 2	Item 1 Exception Margin 2	Item 1 Exception Name 3	Item 1 Exception Margin 3
2	Group 1	$ 156.92	$ 375.42	$ 5.67	$ -
3	Group 2	$ 32.96	$ 91.93	$ -	$ -
4	Group 3	$ 11,783.00	$ 4,118.90	$ 2,907.42	$ -	Company 11396624	$ 1,409.40	Company 11255381	$ 182.76
3 Wk Updated

Desired Result

Start.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2		Owner	Description	Rate	Margin	Current Fee	Minimum	Adjustment	Alternate Fees	Invoice Total
3		Group 1				$ 319.43	$ 250.00			$ 319.43
4			Item 3	24%	$ -
5			Item 1	18%	$ 144.03
6			Item 2	15%	$ 175.40
7			Item 4	5%	$ -
8
9		Group 2
10			Item 3	15%	$ -	$ 725.57	$ 250.00	$ (1.05)		$ 724.52
11			Item 1	18%	$ 684.55
12			Item 2	15%	$ 41.02
13			Item 4	5%	$ -
14			Item 3 - Original	15%	$ -
15			Item 1 - Original	18%	$ 34.01
16			Item 2 - Original	15%	$ 91.93
17			Item 4 - Original	5%	$ -
18			Item 3 - Updated	15%	$ -
19			Item 1 - Updated	18%	$ 32.96
20			Item 2 - Updated	15%	$ 91.93
21			Item 4 - Updated	5%	$ -
22
23		Group 3
24			Item 3	20%	$ 3,028.64	$ 21,147.09		$ 98.92	$ 125.00	$ 21,371.01
25			Item 3 - Exc. 1	10%	$ 1,468.17
26			Item 3 - Exc. 2	10%	$ 190.38
27			Item 1	17%	$ 12,273.59
28			Item 2	17%	$ 4,186.31
29			Item 4	5%	$ -
30			Item 3 - Original	20%	$ 2,907.50
31			Item 3 - Exc. 1 - Original	10%	$ 1,409.40
32			Item 3 - Exc. 2 - Original	10%	$ 182.76
33			Item 1 - Original	17%	$ 11,783.00
34			Item 2 - Original	17%	$ 4,019.90
35			Item 4 - Original	5%	$ -
36			Item 3 - Updated	20%	$ 2,907.42
37			Item 3 - Exc. 1 - Updated	10%	$ 1,409.40
38			Item 3 - Exc. 2 - Updated	10%	$ 182.76
39			Item 1 - Updated	17%	$ 11,783.00
40			Item 2 - Updated	17%	$ 4,118.90
41			Item 4 - Updated	5%	$ -
42
Desired Result

Cell Formulas
Range	Formula
F3	F3	=SUM(E4:E7)
F10	F10	=SUM(E10:E13)
H10	H10	=SUM(E18:E21)-SUM(E14:E17)
J10	J10	=H10+F10
F24	F24	=SUM(E24:E29)
H24	H24	=SUM(E36:E41)-SUM(E30:E35)
J24	J24	=F24+H24+I24

 

[KRice]

Making that change (Margins and Rates on same sheet) wouldn't really affect this approach. The very first step in the Results query combines all four of the transposed tables into one large table. So if a specific column were moved from one sheet to another, it would simply be located on a different row in this large table produced by the Append operation...and any subsequent steps for tagging, grouping, etc. would remain the same. If you're comfortable with the current layouts, I wouldn't recommend changing for any reasons related to producing the Results table.

 

[snuffnchess]

Making that change (Margins and Rates on same sheet) wouldn't really affect this approach. The very first step in the Results query combines all four of the transposed tables into one large table. So if a specific column were moved from one sheet to another, it would simply be located on a different row in this large table produced by the Append operation...and any subsequent steps for tagging, grouping, etc. would remain the same. If you're comfortable with the current layouts, I wouldn't recommend changing for any reasons related to producing the Results table.

Understood will leave the current layout then.

Thanks for explaining.

 

[KRice]

The script currently produces the following output.

Your desired output table includes values for the cells I've highlighted yellow. Do those values simply come from matching the corresponding "Item..." in the block of entries immediately above the yellow blocks?

It appears that your desired output table organizes the items within each output block (current, original, updated) based on the "Rate" sorted in descending order. Is this correct?....and if so, is that important?

Does your actual workbook use the same column heading names? For example, do you have headings with the literal terms "Item 1", "Item 2", etc.? Or is that text used only in your small example?

Start.xlsx
	D	E	F	G	H	I	J	K	L
1
2	Owner	Description	Rate	Margin	Current Fee	Minimum	Adjustment	Alternate Fees	Invoice Total
3	Group 1	Summary			$ 319.43	$ 250.00			$ 319.43
4	Group 1	Item 1	18%	$ 144.03
5	Group 1	Item 2	15%	$ 175.40
6	Group 1	Item 3	24%	$ -
7	Group 1	Item 4	5%	$ -
8	Group 2	Summary			$ 725.57	$ 250.00	$ (1.05)		$ 724.52
9	Group 2	Item 1	18%	$ 684.55
10	Group 2	Item 2	15%	$ 41.02
11	Group 2	Item 3	15%	$ -
12	Group 2	Item 4	5%	$ -
13	Group 2	Item 1 - Original		$ 34.01
14	Group 2	Item 2 - Original		$ 91.93
15	Group 2	Item 3 - Original		$ -
16	Group 2	Item 4 - Original		$ -
17	Group 2	Item 1 - Updated		$ 32.96
18	Group 2	Item 2 - Updated		$ 91.93
19	Group 2	Item 3 - Updated		$ -
20	Group 2	Item 4 - Updated		$ -
21	Group 3	Summary			$ 21,147.09		$ 98.92	$ 125.00	$ 21,371.01
22	Group 3	Item 1	17%	$ 12,273.59
23	Group 3	Item 2	17%	$ 4,186.31
24	Group 3	Item 3	20%	$ 3,028.64
25	Group 3	Item 3 - Exc. 1	10%	$ 1,468.17
26	Group 3	Item 3 - Exc. 2	10%	$ 190.38
27	Group 3	Item 4	5%	$ -
28	Group 3	Item 1 - Original		$ 11,783.00
29	Group 3	Item 2 - Original		$ 4,019.90
30	Group 3	Item 3 - Exc. 1 - Original		$ 1,409.40
31	Group 3	Item 3 - Exc. 2 - Original		$ 182.76
32	Group 3	Item 3 - Original		$ 2,907.50
33	Group 3	Item 4 - Original		$ -
34	Group 3	Item 1 - Updated		$ 11,783.00
35	Group 3	Item 2 - Updated		$ 4,118.90
36	Group 3	Item 3 - Exc. 1 - Updated		$ 1,409.40
37	Group 3	Item 3 - Exc. 2 - Updated		$ 182.76
38	Group 3	Item 3 - Updated		$ 2,907.42
39	Group 3	Item 4 - Updated		$ -
40
ResultsAll

 

[snuffnchess]

The actual workbook does have different column names.... This was just for sample that it was used.

The lines in yellow are using the same margin % as what is listed for the item 1, item 2, item 3, item 4

It is simply pure coincidence that the rate is sorted in descending order. It is essentially different product lines where services are charged at a different percent of margin

 

[snuffnchess]

Sorry for the delay - it has been a day.

This is seriously amazing! And has opened my eyes to a powerful tool that really I should have played with more in the past. It seems like the file is almost there? It is just labeling those percentages in the part marked in yellow?

 

[KRice]

Yes, I haven't been using it very long but it's quite handy. I suspected that the last couple of tweaks might be messy...and they were, as I needed to figure out how to do some things that I've not done before. No doubt this could be improved upon and I fear that it might be slow. In the end, I have several queries. The first four transpose the four input tables. Then another four do these things:

1. One appends all of the transposed tables, creates a grouping/sorting scheme for organizing the information, assigns tags to the Attributes (eventually to become the rows in the output table) and to the Values (eventually to become the columns in the output table), changes the Description (adding Updated or Original) based on certain criteria, and pivots the intermediate table to create the general desired output form.
2. One creates the list of Rates associated with what I term the "Tier 1" block of entries within each group.
3. Another performs summations and moves the results to the "Summary" top row within each owner group
4. The final one merges the first three in different ways to populate missing Rates in the Tier 2 block of entries within each owner group, creates blank rows between owner blocks, sorts the output, and performs final formatting for loading onto a worksheet.

All of the queries except for the last one are now "Connection Only", meaning they will not load onto a worksheet. Instead, each of them produces a virtual table of sorts. I've tweaked the previous code in an attempt to eliminate hard-coding column names that might cause an error. Generally, if you have a scheme for either building a list of column names from the data Source (and then refer to that list) or assign column names within the script (and refer to those names), these types of errors can be eliminated. I've taken the latter approach here.

I uploaded the latest version, available at:

Dropbox

www.dropbox.com

I added a couple more rows to the source tables and inserted some extra columns to see how the script handled the changes. It appears to be working. I have a couple of unpopulated rates in the output table, but I think that may be because I neglected to populate certain cells on the source tables where that information would need to be specified (user error). Let me know if you run into any issues or have questions or suggestions. I'll try to add some documentation to the code to help explain the steps.

 

[snuffnchess]

This is so awesome! Thank you for all of your help!!!

 

[KRice]

I'm happy to help.

 

[KRice]

I revisited the script and split it into several queries because of branching, where an intermediate result is needed for two or more purposes, and each of those proceeds down a different path. I've also provided documentation in the queries to explain the purpose of each query and what the code is doing. The approach taken in this version is generally the same as what was posted before, although after having worked through the problem, I saw several areas for efficiency improvements...and those have been incorporated here. The most significant change involves the initial steps where the data are loaded into Power Query. I opted for an approach that points to an Excel file and all of the worksheets in that file are loaded into a table...and then a filter is immediately applied to select only the four worksheets whose names are "Fixed Data", "Current", "3Wk Original", and "3Wk Updated" (and the names of the tables on those worksheets is immaterial). This allows for application of a function to perform the initial pivot operations on the tables in one step and the code sequences are such that the order of owners does not necessarily have to be the same on the four source worksheets. If desired, this workbook containing the queries and the final output table can exist separately from the workbook that contains the four source worksheets (i.e., the four source worksheets do not need to be in this workbook). The user still needs to modify one line of code in the BaselineTable query: one of the first few lines describes the Source...the full path/name of the Excel workbook that holds the source worksheets. Once that change is made in Power Query, saved, and then PQ is exited by File > Load to worksheet, future updates can be made from the workbook where the final results table is located by executing Data > Refresh All. The current "Source" line in the code has the file/path name that I last used (c:\users\kirkr\documents\@home\hobbies_misc\excelstuff\pqbook.xlsx)...and this file contains the queries/final output table and the source worksheets. In any case, this path/filename will need to be modified.

Dropbox

www.dropbox.com

 

[snuffnchess]

Wow! This is amazing. I will dig into this a bit deeper and play with it ASAP. If I have any questions I'll let you know.

Thank you again.