1 of 1 people found this helpful
There are multiple ways to do this. The first thought that comes to me is to create a calculated field like this:
[Avg Per Sales Rep]
SUM([Sales Amount]) / COUNTD([Sales Rep])
Then you can use a combination of techniques to compare different levels of detail (e.g. Company / Industry). The "best" way to visualize it will depend on your data, your audience, what you want to communicate, etc...
If you want to compare multiple levels of detail, I'm aware of several options.
In the same view:
1. Table Calcs
2. Data Blending
4. Multiple views on a dashboard
If you have a larger sample of data and can post a packaged workbook with a start on what you are trying to accomplish, I'd be happy to take a look!
Would you be able to explain this is a bit more detail please.
I am looking to do a very similar thing and cannot seem to find the best way to achieve it.
Using the 'Sample - Superstore Subset Data', I'm essentially looking to have a dashboard/worksheet with four bars on it:
1. AVG sales for City of choice (e.g. Aberdeen)
2. AVG sales for state of city selected (e.g. South Dakota)
3. AVG sales for region of city selected (e.g. Central)
4. AVG Sales for whole dataset
Essentially a benchmarking dashboard/chart where whatever city you choose the other bars update.
I've tried a few different things, but can't seem to crack it.
Any advise would be greatly appreciated.
There are a couple of ways this might work. Will the filter need to operate on more than one worksheet? Do you want to drive the action from a worksheet, quick-filter, or parameter?
Would you be able to post a sample workbook so I can see which direction you are taking?
Hi Joshua, thanks for the response.
The store (or city) filter will need to apply to either each bar or each worksheet - whichever is the best way to go about this.
I've attached a packaged workbook with a basic example what I'm trying to achieve - Although I'm pretty sure I haven't done this in the best way
Essentially on Dashboard 1 I want to be able in the drop down to be able to pick a city, this will the update that cities bar (in this case Aberdeen), All cities will always stay the same. State will update with whichever state the chosen city is in and the same with region.
So if you pick Aberdeen it will be: Aberdeen, All, South Dakota, Central
if you pick Abilene it will be: Abilene, All, Texas, Central
if you pick Abington it will be: Abington, Massachusetts, East
I hope this helps.
Have you had any luck? Did I clarify what I meant?
Thanks for the reminder to come back to this.
Here's an option for using Data Blending that allows it to all be done in a single view with a single filter. Of course you could break the view into multiple views if you'd like.
I've attached a workbook to demonstrate. Basically, I duplicated the data source for each level of detail I wanted. Then I activated the blend (click the link buttons on the fields to make them active) but only on the fields that define the level of detail I want (e.g. State, Region, or even no linking field to allow it to blend to everything).
Josh, apologies for coming back to you!
This is exactly what I was after and I just couldn't quite get to it! I've repeated what you have done on my data and works brilliantly. Thank you very much for the help.
Glad to help! And glad it worked!