Created table in DAX, but can't extract the correct value

Question

I'm attempting to create a measure to give me the rank of a Salesman by Sales. I'll use the measure in a table where one of the columns is Salesman, so there should be the appropriate row context applied. But using RETURN Calculate( MAXX (RankTable, [Rank] ) ) just gives me a value of 1 for every broker. I can't figure out what to used to just pull out the value for Rank calculated in RankTable. How do I do that?

SumSales = sumx( SalesData, [Sale])
----------
SalesRank = 
VAR SummaryTable = 
    ADDCOLUMNS(
        SUMMARIZE( SalesData, [Salesman] ),
        "Sales", [SumSales]
    )   
VAR RankTable = 
    ADDCOLUMNS(
        SummaryTable,
        "Rank", RANKX( SummaryTable, [Sales])
    )
RETURN 
    Calculate( MAXX (RankTable, [Rank] ) )

I know that RankTable is correct, since DAX Studio give me this result:

Salesman Sales Rank

A 907 1

B 600 3

C 900 2

D 500 4

Here's code for measure mentioned in comments:

Priced. = 
COUNTROWS(
    FILTER( 'Cases',
        [Date Initiated] >= [MinDate]
        && [Date Initiated] <= [MaxDate]
        && not ISBLANK( [Date To Pricing] )
    )
)

SD_ · Accepted Answer · 2021-12-19 22:58:16Z

1

I think you might be overcomplicating things, this should work:

SalesRank = RANKX(ALL('Salesdata'[Salesman]), [SumSales])

Reason you're getting 1 for every line is because in a visual the measure is calculated in the context containing a single salesman. So you need to remove this filter using the ALL() function.

answered Dec 19, 2021 at 22:58

SD_

1389 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Freond Over a year ago

Yep, that works fine! I've been working with DAX for about a year now, and it never fails: When something isn't working, the solution always involves simplifying the code, not adding to it.

Freond Over a year ago

BTW, I still wonder how I would finish off the original code.

Freond Over a year ago

Hmm, the SalesData example was just a small table for testing this. I tried to extend this to the actual production situation. I used Top Priced Rank. = RANKX( ALL( 'Cases'[Broker] ), [Priced.] ) -- and every entry in the table is again 1. The definition for [Priced.] is in the original answer above. I don't see anything that's different from the simpler example. Any idea why I'm again getting all 1's again?

SD_ Over a year ago

Just drop the column name i.e. [Broker] and remove filters from the entire table: RANKX( ALL( 'Cases' ), [Priced] )

Collectives™ on Stack Overflow

Created table in DAX, but can't extract the correct value

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related