Sort legend by current month value for line chart

[Anonymous]

Hi there

I am struggling to sort a line chart legend by the current month value. The chart needs to show the last 6 months worth of data for a list of packages, the latest package value on the chart I want to align with the legend so it is easier for the reader match the the line to the legend colour.

I cannot use a manual entry custom sort for the legend because I want the legends order to update automatically depending on the value of the items e.g. highest to lowest - this will be updated monthly for multiple projects.

I want the legend to be determined by the value, then create a a relationship between the two tables below so I can use the standardised descriptions as the line chart legend

Below example data

Value	Description	Type	Date	Project Name	Package code
100	Package 1	Packages	30/08/2020	A	1.0001
200	Package 2	Packages	30/08/2020	A	1.0002
450	Package 3	Packages	30/08/2020	A	1.0003
200	Package 4	Packages	30/08/2020	A	1.0004
250	Package 5	Packages	30/08/2020	A	1.0005
100	Package 1	Packages	30/07/2020	B	2.0001
100	Package 2	Packages	30/07/2020	B	2.0002
200	Package 3	Packages	30/07/2020	B	2.0003
300	Package 4	Packages	30/07/2020	B	2.0004
700	Package 5	Packages	30/07/2020	B	2.0005
50	Package 1	Packages	30/06/2020	B	2.0001
300	Package 2	Packages	30/06/2020	B	2.0002
400	Package 3	Packages	30/06/2020	B	2.0003
500	Package 4	Packages	30/06/2020	B	2.0004
1,000	Package 5	Packages	30/06/2020	B	2.0005

Package code	Standardised Desc
1.0001	Development
1.0002	Construction
1.0003	Operation
1.0004	Comms
1.0005	Other
2.0001	Development
2.0002	Construction
2.0003	Operation
2.0004	Comms
2.0005	Other

Using this similar request I have created a summurize query based on the below

https://community.powerbi.com/t5/Desktop/Sorting-legend-by-largest-value/m-p/98855#M41657

SummarizePets =
SUMMARIZE ( Table1, Table1[Pet], "Quantity", CALCULATE ( SUM ( Table1[Q] ) ) )

But I dont know how to add the filter for latest month values only, and this adds a third table which I would need to create a relationship with to ensure I can have the Standardised Descriptions as the legend.

Help with adding the DAX filter for latest month so I summurise and sort by this data order or other sugguestions would be much appreciated, thank you! 🙂 

[v-shex-msft · Accepted solution]

Hi @Anonymous,

You can extract the legend fields value to a new table and add the index of these fields with a specific index as you wanted.
Then you can link this field to raw table fields values and use the custom table field on your chart to instead and your graph will display the specific sort order based on the custom table index as you configured.

Custom Sorting in Power BI 

Regards,

Xiaoxin Sheng