We have a dataset for sales/calls where there are a few percentage fields (conversion rate, active rate, etc). The dashboard has three drilldown layers.
The first layer is the office location and sums up calls/sales. The second layer is for supervisors. The third is for the individual sales agents.
The problem I am having is the percentage fields don't work when grouped by date. They turn the grouping into individual rows. I figured if I tried creating a Beast Mode field so it could dynamically calculate it, that it might calculate the percentage on the aggregated data. It doesn't though.
Is it possible to use percentages among aggregated data? Is there some other workaround?
I've roughly mocked up what I think you're discussing. You have a location based top level card, a supervisor level 1st drill, and an agent level final drill. You have data that captures calls and sales for each of these agents with the date, supervisor, location (office), etc. It also sounds like your raw data includes ratios (averages) for each agent. In general, I'd avoid using precaluclated ratios unless you are certain that those ratios are used only at the level of granularity that they are generated at. I'd routinely recommend using a beastmode to generate the same ratios which will be dynamic for the dimensions you've selected in building your card and on-the-fly filters.
I've probably misunderstood some of your question. Please feel free to reply and get me on the right path.
Initially I used a pre-calculated field in the dataset, but when that didn't work, I moved onto a Beast Mode field. Below is a screenshot of how it looks with the beast mode field. The conversion rate formula for the field is X/Y (labelled in my screenshot). The Beast Mode formula is (`totalSTM`/`Inbound_Contacts`).
Ignoring the conversion rate field in the second screenshot, it should look like that, with only one row per location. In my head, the beast mode field would roll up with any aggregation and calculate it based on the aggregated fields, but it doesn't seem to play out that way.
Thanks for the additional context. I think using a beastmode forumla will still give you the flexibilty and accuracy you're looking for. I have used your formula and example data to mock up a few more screens and have highlighed in each the effect of the formula rolled up at different levels of grain.
Screen 1 A and B are shots of the Atlanta region broken out by line item as well as rolled up to a single line.
Screen 2 is a drill on Atlanta to the Supervisor screen
Screen 3 is a drill on Supervisor to the individual Agents.
You should be able to see in the screen shots that the ratio you're seeking is calculated by beastmode in a flexible manner regardless of which level of grain or drill you're on. I hope this helps.