Question about pivot table

[m4u]

Hi all,

I have a revenue table, with those columns: branch,store,month-year,revenue.

now, i need to create a pivot table that should display for each branch (according to date selection), the store with highest revenue.

that means, the pivot should contain only 3 columns: branch,store, revenue - only the store with highest revenue should be displaied.

how could i do that? i tried to use some "Set analisys" technics but without success

thanks a lot,

shlomo

[#2]

Hi schlomo,

Try rank(Sum(Revenue)) in expression

[m4u]

thanks for your answer, but my question is how could i bring also the store name who made the bast sales, not only the rank or revenue, i need to hide all other stores from my report

shlomo

[nstefaniuk]

Hi.

The best is to calculate a flag in the script in my opinion.

However you can create a calculated dimension with a IF clause that compare the sum of each store with the max sum of the stores of each branch, and keep the store only if it is the one that has done the max.

=If(Aggr(sum(revenue), Branch, Store) = Aggr(NODISTINCT max(Aggr(sum(revenue), Branch, Store)), Branch), Store)