Hierarchy sorting

[ivamsikrishna]

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

[v-rzhou-msft]

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 Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CoreyP]

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.

[ivamsikrishna]

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.

[CoreyP · Accepted solution]

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?

[ivamsikrishna]

Thanks for the reply, can use also use this in Matrix correct because we need to add totals and sub-totals. Appreciate for your help.

[ivamsikrishna]

the second table in my post is the matrix I am trying to achieve and first table is the data feed.