How can I display filtered value and grand total in the same sheet

Question

I have a data source which is a database table where I group the number of active customers by SalesOwner. 

What I want to do is filter my sheet using SalesOwner so that I can make a bar chart that contains one of SalesOwners together with the Grand Total. What happens as of now is that when I filter my data and select one SalesOwner, the Grand Total obviously shows the grand total of that one filter.

Is there a way around this? A bar chart with just one of SalesOwners and the total?

Answer 1

You can create a fixed calculated field using {FIXED : COUNTD(wk_customer)}  this will give your required answer with sales owner his total and grand total.

FIXED calculations are applied before dimension filters.  

A better way to represent this chart is by using a percentage calculated field.

1. Select Analysis > Create Calculated Field. Name the calculation Percent of Total, enter the following calculation, and click OK: 

   { FIXED [Segment], [Region] : SUM([wk_customer]) }/{ FIXED [COUNTD(salesowner)] : SUM([wk_customer]) }

2. Right-click Percent of Total in the Measures pane and select Default Properties > Number Format...> Percentage

3. Place Percent of Total on Columns. 

4. Place Region on Rows. 

5. Place Segment on Color.

6. Place Percent of Total on Label.

Comments

Thanks @Machdata..Appreciate your enthusiasm and contribution :)

Answer 2

Hi Atul,

    You can use dual axis graph instead to show the comparison of a single filter variable to grand total.

 By first calculating the grand total by using calculated field.Then following create dual axis graph for grand total and salesowner.

I used a similar data model ,to show the dual axis graph for number of customers for a region (sales owner) over all regions( sales owners ). 

hope this helps you.