topic Re: Creating an "Others" bar with average values by groups in App Development

Creating an "Others" bar with average values by groups

alespooletto — Fri, 29 Nov 2024 14:37:59 GMT

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

Re: Creating an "Others" bar with average values by groups

rubenmarin — Sat, 30 Nov 2024 07:35:00 GMT

Hi, you can use an expression like:

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

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

Re: Creating an "Others" bar with average values by groups

nimishpgupta — Sat, 30 Nov 2024 10:27:44 GMT

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.

Re: Creating an "Others" bar with average values by groups

alespooletto — Mon, 02 Dec 2024 08:08:34 GMT

Hello @nimishpgupta , thanks for the answer.

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

Re: Creating an "Others" bar with average values by groups

nimishpgupta — Mon, 02 Dec 2024 08:36:57 GMT

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

Re: Creating an "Others" bar with average values by groups

MK_QSL — Mon, 02 Dec 2024 08:45:03 GMT

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))
)

Re: Creating an "Others" bar with average values by groups

rubenmarin — Mon, 02 Dec 2024 08:48:22 GMT

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

Re: Creating an "Others" bar with average values by groups

alespooletto — Mon, 02 Dec 2024 09:03:14 GMT

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

Re: Creating an "Others" bar with average values by groups

rubenmarin — Mon, 02 Dec 2024 09:06:00 GMT

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

Re: Creating an "Others" bar with average values by groups

alespooletto — Mon, 02 Dec 2024 09:07:23 GMT

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