Doing a rank with a column created in variable

[felipereyes_]

Hi everyone,

I need to rank the Sales of the Employees, but I have a table and a new column called Test created as a variable in th DAX. This is to improve the performance. I have tried it without and it is taking too long to load, so this is my only solution. The loading time is no problem anymore but now I dont know how to iterate through the new column Test to get the ranking right. I need to use that column (which is a replication of the column Sales) because the column Sales is based on a lot of measures, which is the reason why it takes so long to load.

The query is executing but the rankings come out all as 1. It must be due to the SUMX() that just compares every row with itself. How can I fix this? 

Your help is gretaly apreciated. 

Measure =

VAR table_ =

    ADDCOLUMNS (

        SUMMARIZE (

            'TableEmployee',

            'TableEmployee'[Employee],

            "Sales", 'TableSales'[Sales]

        ),

        "Test", [Sales]

    )

RETURN

RANKX( ALLSELECTED( 'TableEmployee'[Employee] ) , ( SUMX(table_, [Test])),,ASC,Dense)

[Dangar332]

hi, @felipereyes_ 

try below

Measure =
VAR table_ =
    ADDCOLUMNS (
        SUMMARIZE (
            'TableEmployee',
            'TableEmployee'[Employee],
            "Sales", 'TableSales'[Sales]
        ),
        "Test", [Sales]
    )
RETURN
RANKX(table_ ,[test],,ASC,Dense)


     or
measure =
rank(
  crossjoin(
    all('TableEmployee'[Employee]),
    all('TableSales'[Sales])
  ),
orderby([sales],asc)
)

[felipereyes_]

Hi! Thanks for your reply! The first option is not working because the newly created column and table cannot go just like this in the Rankx. That is why I usd SUMX() but I only get 1s for every row. And the second option is not working either, and also referring to the measure Sales, which is wanted to avoid. Do you have any other suggestions?

[Dangar332]

Hi, @felipereyes_ 

provide some data