I have a bit of a conundrum that I was hoping to solve with Level of Detail expressions, but I'm really having a hard time figuring out where to anchor/set the level (or knowing whether it's actually possible). Hopefully one of you can at least point me in the right direction - thank you in advance!!!!
So, what I am setting out to do is
1. Display the total number of times (COUNT) each phrase appears and trend them over time (at the 'quarter' level)
2. Also display (or at least know/calculate/have available) the AVERAGE COUNT for each phrase, at the quarter level, across the entire data set.
The problem I am running into is that, in order to get the answer to 1 (I already have), I have to COUNT (Category), which aggregates it. I am having trouble averaging this correctly at the quarter level to end up with the average number of times each phrase appears in any given quarter. I've included a (large, sorry - couldn't figure out how to attach a file) table with sample data.
The result of the averaging I want to do should be 5.25 for Jelly and 4.75 for Peanut Butter, and then I should be able to display the times that each one occurs per quarter. The actual data set is more complicated; I would like to compare the categories after being filtered by my coworkers to the enterprise-wide data (#2 from above).
Any information/insight would be extremely helpful! Thank you!