Two custom fields divided by Total of Column B

Question & Answer
, ,
1

Hi,

I’m working on a custom calculation, I need to do column A - column B for each row, then divide that by the Total of column B. I can’t figure out how to divide by the total of column B

2

Hi @Matthew_Payne,

If you use sumOver to calculate the total, it will be available in every row and you’ll be able to use it in your division.
sumOver(columnB, , PRE_AGG)

1 Like
3

@David_Wong I have tried that and am getting the incorrect total. There’s about 5 columns that have to be added to column B to correctly filter for the “Total”. Not sure if you would have 5-10 minutes to hop on a call to help me out.

4

Hello @Matthew_Payne, this last part definitely complicates the question. I want to confirm that this is correct, you want to take the difference of Col A and Col B on each row, and divide that by the total value of 5 other columns? The simplest way to do that would be to add a row total for the 5 columns into a new column from your custom SQL, then the function @David_Wong suggested could work. Although you would need to use sumOver for A and B in the subtraction calculation to avoid aggregation issues. If there is a rowID, orderID or something that is different on each row you can use that. The final field would look something like this:
(sumOver({Profit}, [{Order ID}], PRE_AGG)-sumOver({Sales}, [{Order ID}], PRE_AGG))/sumOver({B}, [], PRE_AGG)

I will mark my response as a solution, if you have questions about creating that new column in your dataset please let me know!