Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for
Did you mean:
Contributor II

## Display Top client 3 on combo / Bar chart

Hi All,

I have an issue with creating a combo chart for the top 3 performing clients per year. The dimension is "Year" and the 2 measures are 1. the area chart is client A vs. entire market 2. bar chart shows the top 3 performing clients vs entire market.

the issue is creating an expression that allows me to capture the Top 3 performing clients by revenue per year (TOP 3 revenue per year / total market revenue per year %), Is there a simple way to do this? Thanks.

below chart example of outcome using dummy data.

Labels (6)

• ### Set Analysis

1 Solution

Accepted Solutions
Champion III

try below

1. client revenue vs. the revenue of all clients (Client / ALL Rev

sum(Revenue)
/
sum({<ClientName=>} TOTAL <Year> Revenue)

2. Top 3 performing clients / ALL

sum({<ClientName=>} Aggr(
if ( Rank(Sum({<ClientName=>} Revenue),4,1 ) <4,sum({<ClientName=>}Revenue) )
, Year,ClientName)

/ sum({<ClientName=>} TOTAL <Year> Revenue)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
5 Replies
MVP

Try like this :

Create Variable  vTop = 3

=if(aggr(rank(sum( revenue)),client)<=\$(vTop),client)

Regards

Vikas

If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
Contributor II
Author

Hi Vikas,

Thanks for response but I don't think that would work. You could just use =< 3 rather than create a variable. Also aggr function will only rank the entire data rather than rank the data by each year. I probably did not explain my problem sufficiently enough so that's my fault let me try again please.

The below 1st table is an example of the data set of clients across 3 different years for 5 clients by revenue.

The 2nd table is what I am looking for (dummy data). For each year (Dimension) I want (2 Measures) 1. client revenue vs. the revenue of all clients (Client / ALL Revenue) 2. Top 3 performing clients / ALL Revenue.

How can I group the Top 3 Performing clients for each year? The end goal is the combo chart.

 Year Client Name Revenue 2018 A 2.00 2018 B 1.30 2018 C 1.60 2018 D 1.90 2018 E 2.20 2019 A 2.50 2019 B 2.80 2019 C 3.10 2019 D 3.40 2019 E 3.70 2020 A 4.00 2020 B 4.30 2020 C 4.60 2020 D 4.90 2020 E 5.20

 Year Client A Top 3 Share 2018 16% 46% 2019 16% 47% 2020 15% 47%
Contributor III

Try this,

In Dimension properties there is a option present limitation choose fixed number with top and bottom selection.

Champion III

try below

1. client revenue vs. the revenue of all clients (Client / ALL Rev

sum(Revenue)
/
sum({<ClientName=>} TOTAL <Year> Revenue)

2. Top 3 performing clients / ALL

sum({<ClientName=>} Aggr(
if ( Rank(Sum({<ClientName=>} Revenue),4,1 ) <4,sum({<ClientName=>}Revenue) )
, Year,ClientName)

/ sum({<ClientName=>} TOTAL <Year> Revenue)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Contributor II
Author

Vineeth dude you are legendary 👍 my initial code was similar to yours but I can see what you did differently to mine. Its working at this point in time so I will take it 😀. Finally I can finish this part of the project. You're the man, Thank you!!!!

Tags
Community Browser