How to sort based on my current FY year in power bi

[Sampathkumar_v]

Hi Folks,

My report having two years of data now, created tabluar view while doing sory by sales it's considering both year sales but my requirement is it should sort by only latest year but in excel it's simple while in power bi i am not able to find the option to sory by latest year. 

e.g my product column should sort by 2023 it shouldn't sort by including 2022 and 2023. 

[v-jianboli-msft]

Hi @Sampathkumar_v ,

Please try:

Sum of Value 1 = 
VAR _a =
    MAXX ( ALLSELECTED ( 'Base RCR ADT'[QTR] ), VALUE(RIGHT([QTR],2)))
VAR _b =
    SUMX (
        FILTER (
            ALL ( 'Base RCR ADT' ),
            'Base RCR ADT'[Grp Customer Name] = SELECTEDVALUE ( 'Base RCR ADT'[Grp Customer Name] )
                && VALUE(RIGHT([QTR],2))=_a
        ),
        [revenue in MN]
    )
RETURN
    IF ( ISINSCOPE ( 'Base RCR ADT'[QTR] ), ( [revenue in MN] ), _b ) 

Final output:

Best Regards,

Jianbo Li

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

[Sampathkumar_v · Accepted solution]

Hi Thanks for your support to make this happen. It's working as excepted now.

[v-jianboli-msft]

Hi @Sampathkumar_v ,

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

If this is your original data:

Is this the result you need:

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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

[Sampathkumar_v]

Hi 

Product column should sort by year of 2024. In power bi sort order works based on year column. How can we achive this in power bi? Excepted output from Power bi is below:

Actual sort order works in power bi is below:  Product column sort order happens based on year (2022,2023 & 2024)

[v-jianboli-msft]

Hi @Sampathkumar_v ,

Based on your description, I have created a simple sample:

Please try:

Sum of Value =
VAR _a =
    MAXX ( ALLSELECTED ( 'Table'[Year] ), [Year] )
VAR _b =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Product] = SELECTEDVALUE ( 'Table'[Product] )
                && [Year] = _a
        ),
        [Value]
    )
RETURN
    IF ( ISINSCOPE ( 'Table'[Year] ), SUM ( 'Table'[Value] ), _b )

Then sort by Sum of Value:

Final output:

Best Regards,

Jianbo Li

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

[Sampathkumar_v]

Hi Sir,

Thanks for your guidence when i was tried with same logic into my file not given as excepted. now I have attached the screen shot the way i am looking in to my file (I am not able to attach the PBIX file to make you to understand better) Company FY year start from April to March so accordingly we have breaked the Quarter. 

e.g period name has a direct column from that drived QTR column in 2nd visual QTR we kept in column shelves customer name in Row shevles in values column one of the measure. in 2nd chart how can we sort by only latest Quarter Q1'23,Q2'23,Q3'23 & Q4'23 still i need in my visual  Q1'22,Q2'22,Q3'22 & Q4'22.

Tried below logic: but still no work around. kindly help me on this. 

Sum of Value 1 =

VAR _a =

    MAXX ( ALLSELECTED ( 'Base RCR ADT'[period name] ), [period name] )

VAR _b =

    SUMX (

        FILTER (

            ALL ( 'Base RCR ADT' ),

            'Base RCR ADT'[Grp Customer Name] = SELECTEDVALUE ( 'Base RCR ADT'[Grp Customer Name] )

                && [period name] = _a

        ),

        [revenue in MN]

    )

RETURN

    IF ( ISINSCOPE ( 'Base RCR ADT'[period name] ), ( [revenue in MN] ), _b ) 

[v-jianboli-msft]

Hi @Sampathkumar_v ,

Based on your description, I have modified my sample:

Please try:

Sum of Value 1 = 
VAR _a =
    MAXX ( ALLSELECTED ( 'Base RCR ADT'[QTR] ), VALUE(RIGHT([QTR],2))*10+VALUE(MID([QTR],2,1)))
VAR _b =
    SUMX (
        FILTER (
            ALL ( 'Base RCR ADT' ),
            'Base RCR ADT'[Grp Customer Name] = SELECTEDVALUE ( 'Base RCR ADT'[Grp Customer Name] )
                && VALUE(RIGHT([QTR],2))*10+VALUE(MID([QTR],2,1))=_a
        ),
        [revenue in MN]
    )
RETURN
    IF ( ISINSCOPE ( 'Base RCR ADT'[QTR] ), ( [revenue in MN] ), _b ) 

Sort the visual as mentioned before

Final output:

Best Regards,

Jianbo Li

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

[Sampathkumar_v]

Hi sir,

Thanks for your help make this to resolve 90% bit more concern how can we sort by entire QTR of 2022? for now the solution has provided based on latest QTR of year mean Q4'22.

I mean customer name sort by entire QTR of 2022 (Q1'22,Q2'22,Q3'22 & Q4'22) grand total of all QTR. 

[v-jianboli-msft]

Hi @Sampathkumar_v ,

When you're editing a report in Power BI, you can choose a column to define the sort order of another column.

To set a different column to sort by in Power BI Desktop, both columns need to be at the same level of granularity. For example, to sort a column of month names correctly, you need a column that contains a number for each month. The sort order will apply to any visual in the report that contains the sorted column.

Here is an article shows how to set the sort order of one column by a different column, in Power BI Desktop or in the Power BI service:

Sort one column by another column in Power BI - Power BI | Microsoft Learn

Best Regards,

Jianbo Li

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

[Sampathkumar_v]

Hi,

No this is not my scenario my requirement is that still my tabular view should reflect two years of data in column shelves irrespective of any row shelves if we do sort by normally it will consider two years of value but my ask is that can do do sory by selected year or latest year (e.g 2011 and 2012) only sort order applies 2012 it shouldn't include 2011 while sorting)