bar chart to show Var rate between current year and previous year with top 6 current year selection

[Coloful_Black]

Hi Masters,

I have a table with current years sales and previous years sales crossing companies.

How can I  build up a bar chart to show the CY vs PY variance rate but only to display the top 6 companies with highest CY sales?

[Kushal_Chawda]

Assuming below CY & PY expression

CY: sum({<Year={$(=max(Year))}>}Sales)

PY : sum({<Year={$(=max(Year)-1)}>}Sales)

Create a bar chart

Dimension:

// Check option suppress null values

=aggr(only({<Company ={"=rank(sum({<Year={$(=max(Year))}>}Sales))<7"}>}Company),Company)

Expression:

(sum({<Year={$(=max(Year))}>}Sales)-sum({<Year={$(=max(Year)-1)}>}Sales))/

sum({<Year={$(=max(Year))}>}Sales)

[dplr-rn · Accepted solution]

you could create dimension something like this

if(aggr(rank(sum(LineSalesAmount)),Company)<=6,Company)  exclude null rank and aggr combination to get top 6 companies on sale. replace the sum() with your CY expression

[Coloful_Black]

Hi Kush,

Allow me to spell out the exact fields I have:

Company	CY Sales	PY Sales
A	xxx	xxx
B	xxx	xxx
C	xxx	xxx
D	xxx	xxx
…	…	…

I guess no need for max (year)

I put measure/width expression with "Sum CY/ Sum PY -1 "; I guess it is pretty much the same logic as you provided.

Would you please tweak a bit on the dimension based on the fields at this table?  I tried your provided expression, but did not work...I believe I missed sth....maybe these clear fields may help me better tease out 

Thanks 

[Kushal_Chawda · Accepted solution]

It should be below

Dimension:

// Check option suppress null values

=aggr(only({<Company ={"=rank(sum([CY Sales]))<7"}>}Company),Company)

Expression:

sum([CY Sales])/sum([PY Sales])-1

[Coloful_Black]

Hi Dilipranjith,

It works. But why it does not work on KPI when I put into label...I thought it would return the top 6 companies ' name if it worked for demension.

[Coloful_Black]

Hi Kush,

Thanks for your further clear version. It works!

one more question, as the dimension, I tried to paste it into KPI label, assuming it would return top 6 companies.

But it does not ....anything I miss?