I'm working with some SalesForce Opportunity data and I'm trying to get SUM(closed won opportunity $'s) /SUM(Total Opportunity $'s).
I got the basic logic working correctly, and then I wanted to add a twist and color code this % by opportunity type. That's when I hit a snag. It started calculating each SUM by the opportunity type and then calculating the percentage. what I really want is SUM(closed won opportunity $'s by opportunity type) /SUM(Total Opportunity $'s over all opportunity types).
I tried the window function but the problem with that is that I've got other dimensions involved like years and countries. Using window sums over all years and countries which is no good. I need something more like a PANE based calculation.
I tried to use the table calcs but table calcs only seem to reference a single column and my formula is a % based on two different columns.
Does anyone know how I could accomplish something like this?