cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper IV
Helper IV

sort a mettrix by year and row labels

Hi Gurus, 

 

I have a below metris which I want to share in a way that names of the latest(2021) year besome the first  row of the matrix and name if the last year (2025) become the last row of the matrix

 

for instance SGE S18, Caves Creek and SGE S18, Lens AB should be the first row of the matrix, GBO Expansion should be second row of the matrix, Caves Creeks Floodplain should be the third row of the metrix and so on.. when I sort by facility it does not give me the desire result, it must be something of a custome sort to achieve this (what I think anayway)

 

is there any way I can achieve this.

 

amalrio_1-1605491255927.png

 

 

 

1 ACCEPTED SOLUTION
Microsoft
Microsoft

Hi @amalrio ,

 

Based on your description, you can create a rank column as follows and sort by it.

 

Rank =

if(

    [2021]=BLANK(),

    IF(

        [2022]=BLANK(),

        IF(

            [2023]=BLANK(),

            IF(

                [2024]=BLANK(),

                "5",

                "4"

            ),

            "3"

        ),

        "2"

    ),

    "1"

)

 

Result:

v-yuaj-msft_0-1605661695217.png

 

Hope that's what you were looking for.

Best Regards,

Yuna

 

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

View solution in original post

3 REPLIES 3
Microsoft
Microsoft

Hi @amalrio ,

 

Based on your description, you can create a rank column as follows and sort by it.

 

Rank =

if(

    [2021]=BLANK(),

    IF(

        [2022]=BLANK(),

        IF(

            [2023]=BLANK(),

            IF(

                [2024]=BLANK(),

                "5",

                "4"

            ),

            "3"

        ),

        "2"

    ),

    "1"

)

 

Result:

v-yuaj-msft_0-1605661695217.png

 

Hope that's what you were looking for.

Best Regards,

Yuna

 

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

View solution in original post

Super User IV
Super User IV

@amalrio , You have to create a new sort column based on the order you want

 

new column

Facility 1 = [Facility 1]

Facility sort = Switch( True(),
[Facility] = "SGE S18, Caves Creek",1 ,
[Facility] = "SGE S18, Lens AB ",2 ,
// add others
,6 //default value
)

 

Mark Facility sort as the sort column for Facility

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

 

Sortnewribbion.png

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Solution Specialist
Solution Specialist

Hi, @amalrio 

 

You can click on the column heading again so that the row labels can be sorted in descending order.

If you need customized sorting, you can add an index column to the data source as a sort sequence. For example, SGE S18, Caves Creek and SGE S18, Lens AB corresponds to a value of 1, and then you can use "sort by column" to achieve this.

DavisBI_0-1605492423944.png

 

 

Mark this post as solution if this helps, thanks!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors