1 of 1 people found this helpful
You should be able to use the WINDOW_AVG calculation to get the result you desire. Try something like:
You'll need to set the "Compute Using" to use whatver dimension is on color. If there are other dimensions defining the level of detail, those will also need to be included (that's where having a screenshot that includes the fields in the view or, even better, a packaged workbook would help greatly).
If you're still stuck, please mock-up an example and I'd be happy to take a look!
Happy New Year!
One approach is to use a dual-axis with a set of marks for the labels. To create the below, I
- Created a calculated field Label Position = 0.0 --- since the SUM(Sales) axis is floating point, the Label Position axis must also be floating point to enable the Synchronize Axis option. You can of course adjust this value to adjust the position of the label.
- Created another calculated field Label Value = ... Here I used WINDOW_AVG(SUM(Sales)), because I wanted an aggregate of an aggregate (AVG of SUM(Sales)). If your data isn't aggregated, you might be able to use AVG(Sales), which would be calculated for each pane (panes are created by the blue pills Sub-category and Year in my example)---you don't need to create a formula (IF YEAR==2011 THEN) to do this.
- I also had to wrap the WIDOW_AVG(SUM(...)) in an IF FIRST() == O THEN ... END clause, since I only want one value per mark.
- On the Label Position Marks card,
- Drag Label Value to the Label button. Click the Label button format the labels.
- Change mark type to circle, Color to 0% transparency.
You'll have to play with the label position and formatting. If your data allows, perhaps use a fixed axis to prevent formatting issues when / if the chart is resized.
Either I'm smarter in the New Year or less tired. Either way, the simpler approach to adding the AVG(...) calc next to the year is to just use a discrete blue pill, which will create a pane left of the measure.
It's still a WINDOW_AVG() calc, since you want to aggregate the values across all products to get the AVG SUM(Sales) for each Category, Year dimension. But you don't want the IF FIRST() == 0, since this will generate null rows.
This has been a fun puzzle to work on post New Year. Unlike you, I'm not feeling smarter however since my critical pill won't be blue and I can't make limit the calculation to across and not down. I'm interested in both of your solutions and will use them all the time once I get this figured out....I'm still working on it though.
Okay - So now I've changed my continuous calculation to discrete like Jim Wahl did in his Dotcart workbook. The main difference I'm experiencing is that my calculation formulates across then DOWN no matter what I do. So I'm not getting anything like his neat and tidy averages of all the data points across. My head is all fogged up with this problem...
Do you have the equivalent of "Product Name" on the Detail shelf? If you do then you should be able to use it for compute using. Across/down pane down, etc are all relative compute using and can/will change the calculation when you move the pills around. Where as if you use specific compute using like Jim did then you will get more predictable results.
Hope this helps.
Happy New Year. Did you get this figured out?
I'd be happy to do a short screen sharing session if you're still stuck.