0

# SAP BI/BO - Sort based on break's subtotal

May 08, 2017 at 04:46 PM

163

Former Member

Good afternoon,

I have a structure which has two breaks, then I add the percentual value for a specific value field. What I would like to do, is to sort not just based on the value field - which it does via sort function - but also sort the members of the second break based on the footer's percentual value.

Fact is, I don't really need it to sort based on the value, but rather on its sub-total only. Sorting the value within each break is convenient, but not critical.

How can I achieve this?

can you plz explain with screenshot

Former Member
AMIT KUMAR

Yes I can, I apologize for not having done so before.

Attached is a screenshot, I had to cut most of the columns becuase it is sensitive user information. So basically the first column (highlighted 65) is the first break, within the first break, a second break is created for the second column (for that particular case, of the value 65, the 2nd order beraks correspond to 201.52 and 201.9). The last column is actually a percentage function of the former column. As you can see, for the 2nd break footers were inserted, which include the sub-total of the percentage function in terms of the 1st break. Now when I sort the last column (percentual), it solely organizes the contents of the 2nd break, take the case of the 65 | 201.52, it sorts properly the 2nd break: 82.56% then 17.44%, and 83.06% then 16.92%. However, the 9.98% comes before the 90.02%, that is, it is only sorting within the 2nd break level. In reality, 90.02% > 9.98% so it should place 201.9 before 201.52. Sounds a bit confusing, but the screenie should make it clear.

screen1.png (121.1 kB)

Hi,

I could only think of a workaround that might be helpful.

1. Create a formula using rank : vRank =rank(Measure;Dimension)
2. Insert vRank in the first column
3. Sort and apply Break
4. Hide the vRank column

Here is the another scenario posted in SCN

Thanks,

Jothi

Former Member
Jyothirmayee A

I tried it, both yours and the version on the link, can't seem to get it working.

I even aded a cumulative sum column, and applied the ranking to it with two breaks, nothing seems to work.

Former Member
Former Member

Solved the issue by: computing the 2nd-order break total percentage on each cell. Removed all breaks. Sorted by the mentioned field. Then re-added all the breaks.