Custom Sorting in measures (legend)

[Jit007]

I'm unable to sort the data which comes from calculate measure as per below order,,any idea which steps needs to follow to correct the order

Excellent	1
Good	2
Average	3

[lbendlin · Accepted solution]

Create a static reference table in your data model. Keep it disconnected.

Feed the visual from the first column, Use the measure to find the selected value and return the associated result.

[v-junyant-msft]

Hi @Jit007 ,

@lbendlin Thanks for your concern about this case!

I'll detail what @lbendlin means with test data.
Here is my sample data:

I use this DAX to create the measure:

Here is the Table(2):

And use this DAX to create another meaure:

Measure 2 = MINX(FILTER('Table (2)', 'Table (2)'[level] = [Measure]), 'Table (2)'[rank])

Sorting on measure 2:

But there is a problem with this approach, which is that both measure and measure 2 must exist in your visual object. If you don't want measure 2 to appear in the Table visual object, you can play a little mind trick:

Then click on the edge of the cell where the measure 2 column header is located and pull it inward until you can't see the column.

This way, even though measure 2 is no longer visible, both measure and measure 2 still exist in the table.

Best Regards,
Dino Tao
If this post helps, then please consider Accept both of the answers as the solution to help the other members find it more quickly.

[JItu]

Thanks for your response..but the problem is how to show this in line & stacked column chart..there we might face challege.. please suggest @v-junyant-msft @lbendlin 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Jit007]

I need help to sort by in following order- Excellent >Good >Average

 Thanks @lbendlin @v-junyant-msft 

Sorting.pbix attached file 

[lbendlin]

see attached

[Jit007]

new Sorting (2).pbix revised file attached..

i'm trying to get the out of 100% monthly for ref id data - the total doesn't match to 100%

Thanks in advance @lbendlin 

[lbendlin]

like this?

[Jit007]

like below..Thanks @lbendlin 

emp id	Score	Ref Id	Month	Cat
44	95	1100	Mar	Excellent
44	75	1101	Mar	Good
55	85	1102	Mar	Excellent
55	100	1103	Mar	Excellent
66	45	1104	Mar	Average
66	85	1105	Mar	Excellent
77	100	1106	Mar	Excellent
77	95	1107	April	Excellent
77	50	1108	April	Average
88	45	1109	April	Average
88	50	1110	April	Average
88	65	1111	April	Average
99	100	1112	April	Excellent
99	50	1113	April	Average
99	35	1114	April	Average
100	80	1115	April	Good
100	80	1116	April	Good

[lbendlin · Accepted solution]

[Jit007]

@lbendlin Many thanks for your help..I'm new to PBI world & still learning. Appreciated your help.

[lbendlin · Accepted solution]

Create a static reference table in your data model. Keep it disconnected.

Feed the visual from the first column, Use the measure to find the selected value and return the associated result.