Twice in the past 2 weeks I have had to take two different summary fields in a cross-tab and calculate a ratio of one to the other.\u00a0 The most common example would be when one summary is “Sales” and the other is “Cost of Goods” and you want to calculate a Gross Profit Percent from them.\u00a0 This type of calculation got much easier with CR 2008, because you can now add calculations that draw values from other cells in the cross-tab.\u00a0 The functions that do this is can get pretty complex but this example is not that bad.<\/p>\n
In our example the row and column fields can be anything.\u00a0 All we need are two different summary fields:<\/p>\n
Sum of Sales
\nSum of Cost of Goods<\/p>\n
And for this example the summary fields will be arranged vertically, with the Sales on the top and the Cost of Goods below it.\u00a0 The steps are:<\/p>\n
1) Right click on the Cost of Goods Summary in any cell and select<\/p>\n
Embedded Summary > Insert Embedded Summary<\/p>\n
This should add a third summary row in each cell of the cross-tab. The will all say “Edit this formula”.<\/p>\n
2) Right click on that phrase and select<\/p>\n
Embedded Summary > Edit Calculation Formula<\/p>\n
3) Paste in the following formula:<\/p>\n
Here is how the above formula works.\u00a0 The first two rows read the cross-tab value from 2 rows up (Sum of Sales) and hold that value in a local variable called Sales.\u00a0 That way I can refer to that value several times simply without having to repeat the entire function each time.\u00a0 The next two rows do the same thing with Sum of COGS which is pulled from 1 row up.\u00a0 The last two lines do the actual calculation, including a check to make sure that Sales are not zero.\u00a0 That prevents the ‘divide by zero” error.\u00a0\u00a0 (Note, if your Sales and and COGS fields are currency fields the formula will give you an error, so change the variables to CurrencyVar).<\/p>\n 4) Save the formula and the third summary row in every cell should calculate the ratio based on the two numbers immediately above.\u00a0 The cross-tab will perform the calculation in all group levels of the cross-tab including the grand total rows and columns.<\/p>\n If you need help deploying this formula or need something slightly different, let me know and we can schedule a session to get you what you need.<\/p>\n","protected":false},"excerpt":{"rendered":" Twice in the past 2 weeks I have had to take two different summary fields in a cross-tab and calculate a ratio of one to the other.\u00a0 The most common example would be when one summary is “Sales” and the other is “Cost of Goods” and you want to calculate a Gross Profit Percent from […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,7],"tags":[],"class_list":["post-2751","post","type-post","status-publish","format-standard","hentry","category-formulas","category-method"],"_links":{"self":[{"href":"https:\/\/kenhamady.com\/cru\/wp-json\/wp\/v2\/posts\/2751","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kenhamady.com\/cru\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kenhamady.com\/cru\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kenhamady.com\/cru\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kenhamady.com\/cru\/wp-json\/wp\/v2\/comments?post=2751"}],"version-history":[{"count":0,"href":"https:\/\/kenhamady.com\/cru\/wp-json\/wp\/v2\/posts\/2751\/revisions"}],"wp:attachment":[{"href":"https:\/\/kenhamady.com\/cru\/wp-json\/wp\/v2\/media?parent=2751"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kenhamady.com\/cru\/wp-json\/wp\/v2\/categories?post=2751"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kenhamady.com\/cru\/wp-json\/wp\/v2\/tags?post=2751"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Local NumberVar\u00a0 Sales :=
\nGridValueAt (CurrentRowIndex, CurrentColumnIndex , CurrentSummaryIndex -2 );<\/code><\/p>\nLocal CurrencyVar COGS :=
\nGridValueAt (CurrentRowIndex, CurrentColumnIndex , CurrentSummaryIndex -1 );<\/code><\/p>\nif Sales = 0 then 0 else
\n(Sales - Cogs) % Sales<\/code><\/p>\n