Jump to content
We've recently updated our Privacy Statement, available here. ×

How to do rank by groups dynamically


jihfang WANG

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 1 year later...
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!
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...