Sorting 2nd dimension Issue

[josemaria_cabre]

Hi,

I'm having issues to sort this pivot table (also a stacked bar chart). 

Sorting shoud be: dates with higher KG_TOTAL and then routes operating on each date also sorted from high to low

 Day_Month sort order is working well, I use this expression:

rank(sum($(v.Aux_CYTD)
KG_TOTAL))

But I can't get route sort order to work as you can see:

Which expression shoud I use to sort by second dimension (Route)?

Thanks in advance for your help.

Jose M.

[rubenmarin · Accepted solution]

Hi, field values are sorted globally, so if 'AMS-MAD' it's the second across all dimensions it will show the second in all dimensions, because that's the sort index for that field value.

One workaround to this can be using a calculated dimension that gives a different value to each dimension combination. PFA.

[josemaria_cabre]

Hi Ruben,

It works perfect with your solution 

ps.

I haven't seen this expression on Dim2 before:

=Aggr(Dual(Dim2, RowNo(TOTAL)), Dim1, Dim2)

Thanks for your help!

[piter89]

Hi,

This solution doesn't work if you want to create a column share measure.

I created a share expression:

If(Dimensionality() = 1,
    Sum(Value) / Sum(Total Value)
    ,
    Sum(Value) / Sum(Total<Dim2> Value)
)

Unfortunately, I don't know how to correctly write the share of the second dimension in this table. I want the child row's share to be counted for the parent row. Below is the table I would like to receive:

A	2500	73.53%
B	1500	44,12%1500/2500=60%
A	1000	29,41%1000/2500=40%
B	900	26,47%
...	..	..

Of course, I could use this expression: Sum(Value)/Sum(Total Value), but that's a solution for each row showing the overall share. 

Thanks in advance.