# How to do rank by groups dynamically?

i have the following data:

DateDept

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, and in the value axis, i have the following

SUM(if ((DenseRank(Sum([Cost]) over ([Product]), "desc") <= ${RankFilter}), [Cost]))

i got 'the expression is not valid' error. i can put the 'DenseRank' part in the 'limit data using expression' but it doesn't seem to dynamically change as i apply filter on the dept column.

Any advice is deeply appreciated. Thanks in advance.

+ Add a Comment