How to do rank by groups dynamically

[jihfang WANG]

i have the following data:

 

 

 

Date

Dept

Product

Cost

 

 

 

 

 

D1

A1

...

 

 

 

D1

A2

...

 

 

 

D1

A3

 

 

 

 

D2

A1

 

 

 

 

D2

A2

 

 

 

 

etc

etc

 

 

 

 

 

I would like to generate a bar chart that shows the total spending for each month, the spending can be filtered by the dept column. i would allow the users to get only the top 'n' based on product. so the bar chart shows only the top 'n' products for department D2, for example. so the category axis is month, andin the value axis, i have the following

SUM(if ((DenseRank(Sum([Cost]) over ([Product]), "desc")

[Shuting Fu]

You can add a calculated column "rank" before using it in the y-axis expression. Column "rank" can be calculated by

 

DenseRank(Sum([Cost]) over ([Product]),"desc")Then the y-axis expression can be

 

Sum(if([rank]

[jihfang WANG]

no, this will not work. since the calculated column is static, it will not change when we filter on 'dept'. that is to say, if as a whole, the top 3 spending ranking of product is A1, A2, A3. now if i'm interested only to see what is dept d2's spending, its rank can be A3, A2, A1. this approach will still use A1, A2,A3 as the rank.

 

i need the ranking to be 'dynamic', to change with the filter on department. Thanks.

[Khushboo Rabadia]

You put intersect in your expression like below then it will not throw error:

SUM(if ((DenseRank(Sum([Cost]) over (Intersect([Product])), "desc")

[John Amey]

The use of intersect in this example is very powerful! I spent hours trying to figure out how to apply a dynamic rank and the above formula does it. In my case I was able to add various calculated values to a text box using custom expressions that use the above formula. This text box allows me to show various figures that complement the data visualization. These figures need to calculate using only top 5 values based on the current page filters. This formula works perfectly. Thank you!