Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I Have ש pivot table with three dimensions and one expression,
the table is fully expanded and the third dimension shows only top 10.
I want that the third dimension will be in descending order by the expression.
any idea.
Thanks,
Jacob
Ok Jacob,
For the second and third dimension, please replace them with the following respectively:
=AGGR(DUAL(Group,RowNo(TOTAL)),Shop,Group)
=AGGR(DUAL(Item,RowNo(TOTAL)),Shop,Group,Item)
That will make each value unique within each subgroup
I hope this helps,
Please try and let me know,
Cheers,
Luis
Go to the sort tab; go to ur 3rd dimension;
sort it by expression; tap the measure u're using and sort it as descending:
Hi,
Thanks for the advise, unfortunately it isn't working.
Hi Jacob,
If the values in your dimensions can appear in different groups then the solution that Omar is showing may need some tweaking. Can you share a bit more about it?
Cheers,
Luis
Yes, The third dimension can appear more than once for example
Shop Group Item Qty
A I X 54
Y 58
Z 35
B I X 85
T 42
R 23
II S 55
M 22
L 15
Thanks.
Ok Jacob,
For the second and third dimension, please replace them with the following respectively:
=AGGR(DUAL(Group,RowNo(TOTAL)),Shop,Group)
=AGGR(DUAL(Item,RowNo(TOTAL)),Shop,Group,Item)
That will make each value unique within each subgroup
I hope this helps,
Please try and let me know,
Cheers,
Luis
Thanks, but still not working
you might forgot to mention: sum(qty) any where?
Hi,
Yeap you're right. I'm assuming you're following Omar's recommendation (Sorting by expression with Sum, etc.)
It's the same that Omar said but the 2nd and 3rd dimensions on your Pivot Table needed to be changed
Please try and let me know
Cheers,
Luis
Hi,
Tried without a success.
=AGGR(DUAL(Group,RowNo(sum(TOTAL Qty))),Shop,Group)
=AGGR(DUAL(Item,RowNo(sum(TOTAL Qty))),Shop,Group,Item)
Hehe, not like that,
Is it possible for you to share data?