(Note to self: V 10.0 workbook here.)
When you use a table calc as a filter, you don't lose the underlying table.
In sheet 1 I have your data displayed. And in the title I have an average of all the data.
In sheet 2 I used LOOKUP to select one company, controlled by a parameter. LOOKUP is a table calc. You can see that even though one company is displayed, the overall average value in the title remains intact. This lookup assigns a value of 1 or 0 depending on the company, and the actual filter values would be either 1 or 0, and I select for 1. I don't display the filter. I display the parameter for the user to select from.
In sheet 3 I used a different LOOKUP to allow for multiple selections. This LOOKUP becomes the filter itself, and I have displayed it as a filter. (You could actually set this up as a single value filter, effectively acting exactly like the parameter in Sheet 2.)
template A.twbx 24.2 KB
It is interesting to see you added average revenue with company revenue in one sheet.
But actually what I'm looking for is the visualization of the company revenue, average revenue, the maximum revenue of all companies and the minimum revenue of all companies in one chart.
It could be using a circle or bar to represent company revenue, and markers to represent MAX, MIN & AVG.
Is there any way to implement it? Thanks a lot!
You'll make another calc to do MIN revenue just like I did AVG. Likewise MAX.
And then it's just a matter of creativity to display what you need -- whether it's all on one sheet, or on separate sheets. I can't picture what you are really shooting for, so I took a stab at it in Sheet 4 of the attached. Really the actual visualization is a matter of what your users want to see, not what I think they want to see. And it should be left as an exercise for you to shape your visualization as you see fit. I made a MIN and a MAX calc, and then I used dual-axis to overlay the table calcs on a bar.
I just gave you the tools you need (specifically using a table calc as a filter) to give you the necessary access to all your data while limiting what gets displayed.
template B.twbx 28.0 KB
The sheet 4 is exactly what I need! It's really helpful.
Now I only have one small question about the visualization. If I want to mark max & min in Gantt Bar, Ave into Plus shape and Your company into Circle, how can I implement. Now I can only figure out to dual Axis two groups not three groups.
Dual axis will only let you crunch two chunks of stuff together. And you can specify one type of formatting for each chunk. (So I did bar with one axis, and shapes with the MeasureNames axis.)
See Sheet 5 of the attached. I moved the MAX and MIN from the Measure Names pill into reference lines on the sum(Revenue) axis. That leaves the AVG calc as its own axis.
Because there are table calcs involved, I can't get the two axes to synchronize, so I also put the MAX as a reference line on the AVG axis. (I formatted it so that there is no actual line and no actual label, but it's still on there.) So each axis has the same max value and they line up because of that. It's a hack way to force axes to align.
So it's not exactly a Gantt, but you effectively have the same sort of visualization. Now the sum for the company is a circle. (You can mess with size and color, etc.) And the AVG is a plus.
template C.twbx 30.9 KB