In this example, we'll build a single pivot table to see a few donation metrics by month. We'll be adding formula values to a data table, but custom formulas can also be added to other chart types.
To start, let's add a row for donation date grouped by month. Next, we'll add a value for the total donation amount.
Now, let's add a few custom formula values to dig deeper. We'll click to add a new value, and instead of adding a data field, click the function icon to open the formula editor.
The formula editor has two very helpful features. First, you can search and select acceptable data fields, as well as functions. Second, you'll only be able to save a valid formula. This is a helpful step to ensure your expression syntax is correct.
For the first formula value, let's show the donation amount of just recurring donations each month. To start, search and select donation amounts "some". Next, we'll want to filter this value by another donation attribute. In this case, whether or not the donation is recurring. We'll wrap this value in parentheses and add it. Filter data points separated by a comma. We'll add recurring, which is a boolean field we can filter—true or false. With this field added, we can select the data field label and adjust the filtering to "true". Now this formula will result in the total donation amount where recurring equals "true". And since this is currency, we'll edit the numeral to display as currency.
Next step, let's create a more complex formula using math. In this next example we'll create a formula to calculate the percentage that the Funraise Operations Tip feature is contributing towards our overall transaction fee cost per month. For simplicity, we'll assume the total credit card and transaction fees are three percent.
First, we'll need to find out the total donation fee amount by multiplying our total donation amount by three percent. Next, we'll find the total amount collected as an Operations Tip by filtering by this allocation. When we divide this by our total fee cost calculation, we'll see the percentage of the fee that was covered by donors who added an Operations Tip. We can also filter this value to only include online donations where a fee occurs. Now we can see the percentage of transaction fees we've covered with the Operations Tip feature.
For one more fun trick, let's bring this column to life with conditional formatting. We'll color the column green if the Operations Tip covered 100 percent or more of the fees, and color it red if the Operations Tip covered less than the fees. We can do that by clicking the value "color" and selecting conditional, because this is a percentage. If the value is less than one, we want it to be red. If it's more than or equal to one, we want it to be green. Now we can quickly see which months were up and down. Conditional formatting can make for more user friendly reports.
There's much more you can accomplish with formulas. These are just a few basic concepts. Have fun!
