MATRIX SORTING

[Gareth_Hunt]

Hi there

I have a setup where Im using revenue measure and a Date Filter to look at annual revenues over sseveral years 

YS-Revenue = CALCULATE(SUM('invoice]),DATEADD('DCalandar'[Date], 0, year))

I then want to use a matrix to have Year on year comparision

                      2018                                         2019

CustomerA     Revenue value 2018                 Revenue value 2019

CustomerB     Revenue value 2018                 Revenue value 2019

eg

                  2018                                    2019

                  YS-revenue                          YS-revenue

Hunters     £506                                     £405

Sams         £506                                     £607

I want to be able to sort by the 2019 revenue to give  like this 

                2018                                    2019

                  YS-revenue                          YS-revenue

Sams         £506                                     £607

Hunters     £506                                     £405

I have set the sort by to sort decending and selected YS revenue  but I dont have success

if I remove year column from the matrix then the revenue decends perfectly but I want to show year on year as in the example

help please!

[v-eqin-msft · Accepted solution]

Hi @Gareth_Hunt ,

From my test, I could sort decending correctly by YS revenue. Please try:

Method1:

YS-Revenue =
CALCULATE (
    SUM ( DCalandar[Invoice] ),
    FILTER (
        'DCalandar',
        'DCalandar'[Customer] = MAX ( 'DCalandar'[Customer] )
            && YEAR ( 'DCalandar'[Date] ) = YEAR ( MAX ( 'DCalandar'[Date] ) )
    )
)

Method2：Calculate the sum separately:

2018 YS-revenue =
CALCULATE (
    SUM ( 'DCalandar'[Invoice] ),
    FILTER (
        'DCalandar',
        'DCalandar'[Customer] = MAX ( 'DCalandar'[Customer] )
            && YEAR ( 'DCalandar'[Date] ) = 2018
    )
)

2019 YS-revenue =
CALCULATE (
    SUM ( 'DCalandar'[Invoice] ),
    FILTER (
        ALL ( 'DCalandar' ),
        'DCalandar'[Customer] = MAX ( 'DCalandar'[Customer] )
            && YEAR ( 'DCalandar'[Date] ) = 2019
    )
)

Method3：Use rank

Measure=
RANKX ( ALL ( 'DCalandar' ), [2019 YS-revenue],, DESC, DENSE )

Here is the pbix file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.

Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Eyelyn Qin

[amitchandak]

@Gareth_Hunt , Create a sort column  on year

Year 1 = [year]

year sort = rankx(all(table), [year]),,desc)

sort year 1 on year sort and and use year 1 on column

[AlB]

Hi @Gareth_Hunt 

Can you share the pbix, or one with mock data that reproes the issue?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers