Ranking in columns

Hi All I need ranks in columns ,below is the sample data and last three columns are the required ranks 1. Rank of employee sales compared to all employees in the firm 2.Rank of employee sa...

Need Help

Anonymous
6 years ago
Hi @Truelearner ,

Create Calculated Columns.

Venatas Employee Wise = CALCULATE(SUM('Table'[Ventas]),ALLEXCEPT('Table','Table'[Emp]))

Rango de la firma Emp1 = RANKX(ALL('Table'[Emp],'Table'[Venatas Employee Wise]),'Table'[Venatas Employee Wise],,ASC)

Venatas Department Wise = CALCULATE(SUM('Table'[Ventas]),ALLEXCEPT('Table','Table'[Departamento]))

Rango de Emp en el Departamento1 = RANKX(FILTER('Table','Table'[Departamento] = EARLIER('Table'[Departamento])),'Table'[Venatas Employee Wise],,ASC)

Rango de Departamento de Emp1 = RANKX(ALL('Table'[Departamento],'Table'[Venatas Department Wise]),'Table'[Venatas Department Wise],,ASC)

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

mahoneypat

6 years ago

Since I had already started on these before seeing the post from Anonymous , I will post these alternate solutions. The difference is that these calculate it directly w/o an intermediate column. Potentially a good example of using the Value term in RANKX on a virtual table (I learned something new on this one).

Emp Firm Rank New =
VAR summarytable =
    ADDCOLUMNS (
        SUMMARIZE ( Ranks, Ranks[Emp] ),
        "@Sales", CALCULATE (
            SUM ( Ranks[Sales] ),
            ALL ( Ranks ),
            Ranks[Emp] = EARLIER ( Ranks[Emp] )
        )
    )
VAR currentcontextsales =
    CALCULATE (
        SUM ( Ranks[Sales] ),
        ALL ( Ranks ),
        Ranks[Emp] = EARLIER ( Ranks[Emp] )
    )
RETURN
    RANKX ( summarytable, [@Sales], currentcontextsales, ASC )

Emp Rank in Dept New =
VAR summarytable =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( Ranks, Ranks[Dept] = EARLIER ( Ranks[Dept] ) ),
            Ranks[Emp]
        ),
        "@Sales", CALCULATE (
            SUM ( Ranks[Sales] ),
            ALL ( Ranks ),
            Ranks[Emp] = EARLIER ( Ranks[Emp] )
        )
    )
VAR currentcontextsales =
    CALCULATE (
        SUM ( Ranks[Sales] ),
        ALL ( Ranks ),
        Ranks[Emp] = EARLIER ( Ranks[Emp] )
    )
RETURN
    RANKX ( summarytable, [@Sales], currentcontextsales, ASC )

Emp Dept Rank New =
VAR summarytable =
    ADDCOLUMNS (
        SUMMARIZE ( Ranks, Ranks[Dept] ),
        "@Sales", CALCULATE (
            SUM ( Ranks[Sales] ),
            ALL ( Ranks ),
            Ranks[Dept] = EARLIER ( Ranks[Dept] )
        )
    )
VAR currentcontextsales =
    CALCULATE (
        SUM ( Ranks[Sales] ),
        ALL ( Ranks ),
        Ranks[Dept] = EARLIER ( Ranks[Dept] )
    )
RETURN
    RANKX ( summarytable, [@Sales], currentcontextsales, ASC )

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Forum Discussion

Ranking in columns

Related content

Duplicate ranking

Rank with conditions

Dynamic Ranking

Ranking KPI card

What are community ranks?