Creating an "Others" bar with average values by groups

[alespooletto]

Hello, 

I currently have a very simple bar chart counting the number of tickets opened by specific people. I want to show the top 5 users, but at the same time show an "Others" bar that has the average of tickets opened by other people. 

So right now its like this 

And I woudl like to visualize it like this, where the red shows the average of tickets opened by the rest of the people. I don't necessarily need help with this measure, but more with how to make this happen? 

Because in the graph, I only see the option to add others but not to choose what the others represent 

[rubenmarin]

Hi, you can use an expression like:

Avg(Aggr(Count(YKY_Contact),[DimensionField]))

DimensionField is the field used as dimension in the bar chart.

[nimishpgupta]

You can create two different expression, sample code below

Tickets:
Load * Inline
[

User,TicketNo
A,A1
A,A2
A,A3
A,A4
A,A5
A,A6
B,B1
B,B2
B,B3
B,B4
B,B5
C,C1
C,C2
C,C3
C,C4
D,D1
D,D2
E,E1
E,E2
F,F1
F,F2
F,F3

];

Chart Expressions:

Top N:

count({<User = {"=Rank(count(TicketNo))<=3"}>}TicketNo)

Average:
avg(aggr({<User = {"=Rank(count(TicketNo))>3"}>}count(TicketNo),User))

Limit the dimension to show only Top N Users

Below is the output Chart

You can create Master Measures to customise the colors of the Bars.

[alespooletto]

Hello @nimishpgupta , thanks for the answer. 

Can you tell me where should I put those measures you created? 

[nimishpgupta]

It has to be added as measures to your bar chart, two seperate measures

[MK_QSL]

Add an extra Inline table as below

DIM:
Load * Inline
[
Dimension
User
Average
];

Now create bar chart as 

Dimension 

=PICK(Match(Dimension, 'User','Average'), User, 'Average')

Expression

IF(Dimension = 'User',
Count({<User = {"=Rank(Count(Distinct Ticket)) <= 3"}>}Distinct Ticket),
Avg(Total Aggr(Count({<User = {"=Rank(Count(Distinct Ticket)) > 3"}>}Distinct Ticket),User))
)

[rubenmarin]

Have you tried my answer? You only need to use this expression: Avg(Aggr(Count(YKY_Contact),[DimensionField]))

[alespooletto]

Thanks for your suggestion @rubenmarin , but it's not clear where should I add your measure?

[rubenmarin · Accepted solution]

That's the only measure the chart needs.

Instead of "Count(TKT_Contact)" use "Avg(Aggr(Count(TKT_Contact),[DimensionField]))", where DimensionField is the field used as dimension in the bar chart

[alespooletto]

Okay, this is definitely the easiest option. Thanks Ruben!