Forum Discussion
Hierarchy sorting
Team, we are trying to develop typical FI report which required detailed level of custom sorting order to match client needs. Below is the sample data.
| Segment | Main | Sub | Order Number | Desc | Segment1 |
| Segment1 | First | Strawberry | 30000 | FSSBerry | $5,330.00 |
| Segment1 | First | Blueberry | 30100 | FSBBerry | $2,541.00 |
| Segment1 | First | Blackberry | 30200 | FSBLBerry | $1,254.00 |
| Segment1 | First | Raspberry | 30300 | FLRBerry | $8,745.00 |
| Segment1 | First | Pineberies | 30400 | FSPBerry | $2,365.00 |
| Segment1 | First | Veggie | 30500 | FSVeggie | $1,458.00 |
| Segment1 | First | Other | 30800 | FSCherry | $4,752.00 |
| Segment1 | First | Other | 30900 | FSDoc | $252,155.00 |
| Segment1 | First | Other | 31200 | FSSNKS | $22,112.00 |
| Segment1 | First | Other | 32000 | FSCRDT | $25,633.00 |
| Segment1 | First | Strawberry | 33000 | RSSBERRY | $255,256.00 |
| Segment1 | First | Blueberry | 33100 | RTBBerry | $98,574.00 |
| Segment1 | First | Blackberry | 33200 | RTBLBerry | $45,215.00 |
| Segment1 | First | Raspberry | 33300 | RTRBerry | $239,874.00 |
| Segment1 | First | Pineberies | 33400 | RTPBerry | $4,655.00 |
| Segment1 | First | Veggie | 33500 | RTVeggie | $288,745.00 |
| Segment1 | First | Strawberry | 34000 | FTSBerry | $56,621.00 |
| Segment1 | First | Strawberry | 34050 | FTASBerry | $68,745.00 |
| Segment1 | First | Blueberry | 34100 | FTOBLBerry | $51,235.00 |
| Segment1 | First | Blueberry | 34150 | FTABLUBerry | $485,145.00 |
| Segment1 | First | Blackberry | 34200 | FTBLBerry | $148,752.00 |
| Segment1 | First | Blackberry | 34250 | FTABLBerry | $2,365,987.00 |
| Segment1 | First | Raspberry | 34300 | FTRSBerry | $545,635.00 |
| Segment1 | First | Raspberry | 34350 | FTARABerry | $1,254,123.00 |
| Segment1 | First | Veggie | 34500 | FTOVeggie | $1,587.00 |
| Segment1 | First | Veggie | 34550 | FTAVeggie | $4,562.00 |
| Segment1 | First | Other | 34800 | FTADJ | $5,874.00 |
| Segment2 | First | Strawberry | 30000 | FSSBerry | $4,280.00 |
| Segment3 | First | Blueberry | 30100 | FSBBerry | $1,491.00 |
| Segment4 | First | Blackberry | 30200 | FSBLBerry | $204.00 |
| Segment5 | First | Raspberry | 30300 | FLRBerry | $7,695.00 |
| Segment6 | First | Pineberies | 30400 | FSPBerry | $1,315.00 |
| Segment7 | First | Veggie | 30500 | FSVeggie | $408.00 |
| Segment8 | First | Other | 30800 | FSCherry | $3,702.00 |
| Segment9 | First | Other | 30900 | FSDoc | $251,105.00 |
| Segment10 | First | Other | 31200 | FSSNKS | $21,062.00 |
| Segment11 | First | Other | 32000 | FSCRDT | $24,583.00 |
| Segment12 | First | Strawberry | 33000 | RSSBERRY | $254,206.00 |
| Segment13 | First | Blueberry | 33100 | RTBBerry | $97,524.00 |
| Segment14 | First | Blackberry | 33200 | RTBLBerry | $44,165.00 |
| Segment15 | First | Raspberry | 33300 | RTRBerry | $238,824.00 |
| Segment16 | First | Pineberies | 33400 | RTPBerry | $3,605.00 |
| Segment17 | First | Veggie | 33500 | RTVeggie | $287,695.00 |
| Segment18 | First | Strawberry | 34000 | FTSBerry | $55,571.00 |
| Segment19 | First | Strawberry | 34050 | FTASBerry | $67,695.00 |
| Segment20 | First | Blueberry | 34100 | FTOBLBerry | $50,185.00 |
| Segment21 | First | Blueberry | 34150 | FTABLUBerry | $484,095.00 |
| Segment22 | First | Blackberry | 34200 | FTBLBerry | $147,702.00 |
| Segment23 | First | Blackberry | 34250 | FTABLBerry | $2,364,937.00 |
| Segment24 | First | Raspberry | 34300 | FTRSBerry | $544,585.00 |
| Segment25 | First | Raspberry | 34350 | FTARABerry | $1,253,073.00 |
| Segment26 | First | Veggie | 34500 | FTOVeggie | $537.00 |
| Segment27 | First | Veggie | 34550 | FTAVeggie | $3,512.00 |
| Segment28 | First | Other | 34800 | FTADJ | $4,824.00 |
we try and expecting in below order in Matrix visualization, we try applying different sorting options but not successful because of multiple sub sections are with same name but client would like to see data sorting on order number. They don't want to group things by Sub section.
| Main | Sub | Order Number | Desc | Segment1 | Segment2 |
| First | Strawberry | 30000 | FSSBerry | $5,330.00 | $4,280.00 |
| First | Blueberry | 30100 | FSBBerry | $2,541.00 | $1,491.00 |
| First | Blackberry | 30200 | FSBLBerry | $1,254.00 | $204.00 |
| First | Raspberry | 30300 | FLRBerry | $8,745.00 | $7,695.00 |
| First | Pineberies | 30400 | FSPBerry | $2,365.00 | $1,315.00 |
| First | Veggie | 30500 | FSVeggie | $1,458.00 | $408.00 |
| First | Other | 30800 | FSCherry | $4,752.00 | $3,702.00 |
| First | Other | 30900 | FSDoc | $252,155.00 | $251,105.00 |
| First | Other | 31200 | FSSNKS | $22,112.00 | $21,062.00 |
| First | Other | 32000 | FSCRDT | $25,633.00 | $24,583.00 |
| First | Strawberry | 33000 | RSSBERRY | $255,256.00 | $254,206.00 |
| First | Blueberry | 33100 | RTBBerry | $98,574.00 | $97,524.00 |
| First | Blackberry | 33200 | RTBLBerry | $45,215.00 | $44,165.00 |
| First | Raspberry | 33300 | RTRBerry | $239,874.00 | $238,824.00 |
| First | Pineberies | 33400 | RTPBerry | $4,655.00 | $3,605.00 |
| First | Veggie | 33500 | RTVeggie | $288,745.00 | $287,695.00 |
| First | Strawberry | 34000 | FTSBerry | $56,621.00 | $55,571.00 |
| First | Strawberry | 34050 | FTASBerry | $68,745.00 | $67,695.00 |
| First | Blueberry | 34100 | FTOBLBerry | $51,235.00 | $50,185.00 |
| First | Blueberry | 34150 | FTABLUBerry | $485,145.00 | $484,095.00 |
| First | Blackberry | 34200 | FTBLBerry | $148,752.00 | $147,702.00 |
| First | Blackberry | 34250 | FTABLBerry | $2,365,987.00 | $2,364,937.00 |
| First | Raspberry | 34300 | FTRSBerry | $545,635.00 | $544,585.00 |
| First | Raspberry | 34350 | FTARABerry | $1,254,123.00 | $1,253,073.00 |
| First | Veggie | 34500 | FTOVeggie | $1,587.00 | $537.00 |
| First | Veggie | 34550 | FTAVeggie | $4,562.00 | $3,512.00 |
| First | Other | 34800 | FTADJ | $5,874.00 | $4,824.00 |
Maybe I'm a little lost. From what I understand, the client doesn't care how any of the other fields are sorted, they only want to see the visual sorted by order number in ascending order? Can you confirm and also show a screenshot of your matrix visual and the fields included?
6 Replies
I feel like you should be able to just sort the visual by the order number column in descending order. If for some reason that doesn't work, you can always create calculated columns in your tables to use as sort by columns.
We tried that but because of the Sub column (where the subjects are repeated and we cannot apply different sorting order on same field), any other work around could be helpful. Appreciate.
Maybe I'm a little lost. From what I understand, the client doesn't care how any of the other fields are sorted, they only want to see the visual sorted by order number in ascending order? Can you confirm and also show a screenshot of your matrix visual and the fields included?
Hi ivamsikrishna ,
Please try code as below to create measures.
Segment 1 = CALCULATE ( SUM ( 'Table'[Segment1] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Sub], 'Table'[Desc] ), 'Table'[Segment] = MIN ( 'Table'[Segment] ) ) )Segment 2 = CALCULATE ( SUM ( 'Table'[Segment1] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Sub], 'Table'[Desc] ), 'Table'[Segment] = MAX ( 'Table'[Segment] ) ) )Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
