Hi Team,
I have two filters on sheet,(Unit and Subunit).
I have a pivot Table with 2 dimensions (Accounts ,IOU)
When user selects Unit , I need to show top10 accounts in it, but at unit level values should be based on all the accounts which are part of that Unit irrespective of ranking in block .
I have achieved ranking of top 10 accounts by using aggr() in account dimension but values at IOU also shows based on top 10 of accounts because of ranking inside dimension. Kindly help how to show values at unit level irrespective of restriction of accounts.
Ranking applied in accounts dimension:
=If(aggr(Num(Aggr(Rank(TOTAL Sum(Total Blocks), 4, 1),Unit, Accounts)),Unit,Accounts) <=10,Accounts,Null())
Measure: Sum(Total Blocks)
