Forum Discussion

Gareth_Hunt's avatar
Gareth_Hunt
Helper II
5 years ago
Solved

MATRIX SORTING

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!
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

3 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    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 

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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