Creating custom columns for use in tables
End Goal: Our goal is to create a table that can successfully group elements by row (such as state) and implement column values that are based on custom formulas. The input data for this table consists of our company's sales data.
What I am attempting to do currently is create a pivot table that groups our monthly sales by Sales Region (rows), and then the various columns are various units of measure (MTD cost, prior year MTD cost, etc.)
Challenge: It has been fairly easy for me to create a column for MTD cost, as that is simply "sum of revenue" with a date filter for the current month. However, we want to begin to implement more complex logic into columns. One example of such logic is a column called "MTD trending."
Requirements: The data MUST be groupable by rows. We MUST be able to create custom measures and add them as volumn values within the table.
MTD Trending = ((MTD Revenue / number of business days elapsed in current month) * number of total business days in current month) / MTD Quota)
The end result is a % value of the MTD quota that we are expected to finish near. How might I accomplish the creation of this column (and other columsn with complex formulas) within a sumo table?