Measure for Sorting which differs each month

Hi KW123 

Create the following calculated table 

Placing = GENERATESERIES ( 1, 100, 1 )

 

Add a matrix visual. Place 'Table'[Month] in the columns and Placing[Value] in the rows. Don't panic when you see the error 😊

create the flowing measures and place them in the values of the matrix. 

Name Measure =
VAR T1 =
SUMMARIZE ( 'Table', 'Table'[Name], "@Sold", [$Sold] )
VAR NameCount =
COUNTROWS ( T1 )
VAR CurrentPlacing =
MAX ( Placing[Value] )
VAR Result =
IF (
CurrentPlacing <= NameCount,
MAXX (
TOPN (
1,
TOPN ( CurrentPlacing, T1, [@Sold], DESC, 'Table'[Name], ASC ),
[@Sold], ASC,
'Table'[Name], DESC
),
'Table'[Name]
)
)
RETURN
Result

$Sold New Measure =
VAR T1 =
SUMMARIZE ( 'Table', 'Table'[Name], "@Sold", [$Sold] )
VAR NameCount =
COUNTROWS ( T1 )
VAR CurrentPlacing =
MAX ( Placing[Value] )
VAR Result =
IF (
CurrentPlacing <= NameCount,
MAXX (
TOPN (
1,
TOPN ( CurrentPlacing, T1, [@Sold], DESC, 'Table'[Name], ASC ),
[@Sold], ASC,
'Table'[Name], DESC
),
[@Sold]
)
)
RETURN
Result

Month Measure =
IF ( NOT ISBLANK ( [Name Measure] ), MAX ( 'Table'[Month] ) )

You may also want to add the Placing

Placing Measure =
IF ( NOT ISBLANK ( [Name Measure] ), MAX ( Placing[Value] ) )

Hi All，
Firstly  tamerj1 thank you for your solution!
And KW123 ,as I understand it, is that how your dataset is structured, and then you want to ask for the sales of the people for each month to be sorted is that right? 

Below is the dataset I created modelled after your example data data.

Then we sort by each month and each month's sales.

RankPerMonth = 
 RANKX(
    FILTER(
        ALL('Table'),
        'Table'[Month  ]=MAX('Table'[Month  ])),
        CALCULATE(
            SUM('Table'[ $  Sold  ])),,DESC)

I hope my answer can solve your problem, if you still have more recent asynchronous needs, you can contact me at any time, I will be the first time to reply after receiving the message!

Hope it helps!

 

Best regards,
Community Support Team_ Tom Shen

 

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

 

 

 

Hi Anonymous 
Thank you for your solution. 
I'm not in a place to answer on behalf of the requester KW123. However, I think the requirements are a little bit different than what you have kindly proposed. Attached sample file explains my solution which seems to fully address the requirements.

 

$Sold New Measure = 
VAR T1 =
    SUMMARIZE ( 'Table', 'Table'[Name], "@Sold", [$Sold] )
VAR NameCount =
    COUNTROWS ( T1 )
VAR CurrentPlacing =
    MAX ( Placing[Value] )
VAR Result =
    IF (
        CurrentPlacing <= NameCount,
        MAXX (
            TOPN (
                1,
                TOPN ( CurrentPlacing, T1, [@Sold], DESC, 'Table'[Name], ASC ),
                [@Sold], ASC,
                'Table'[Name], DESC
            ),
            [@Sold]
        )
    )
RETURN
    Result

Name Measure = 
VAR T1 =
    SUMMARIZE ( 'Table', 'Table'[Name], "@Sold", [$Sold] )
VAR NameCount =
    COUNTROWS ( T1 )
VAR CurrentPlacing =
    MAX ( Placing[Value] )
VAR Result =
    IF (
        CurrentPlacing <= NameCount,
        MAXX (
            TOPN (
                1,
                TOPN ( CurrentPlacing, T1, [@Sold], DESC, 'Table'[Name], ASC ),
                [@Sold], ASC,
                'Table'[Name], DESC
            ),
        'Table'[Name]
        )
    )
RETURN
    Result

Month Measure = 
IF ( NOT ISBLANK ( [Name Measure] ), MAX ( 'Table'[Month] ) )

Placing Measure = 
IF ( NOT ISBLANK ( [Name Measure] ), MAX ( Placing[Value] ) )