Forum Discussion

Antonio_Gomez's avatar
Antonio_Gomez
Resolver I
3 years ago
Solved

TOP N Disappear

Hello everyone,

 

I need some help, please.

 

I'm trying to create a table with Top N depending of the amount of employees.

 

This is my measure:

------------------------

TopNPracticos =

VAR CantPract =
CALCULATE(
COUNT(Empleado[CodigoPractico])
)


VAR TopNValue = ROUNDUP(CantPract/2,0)

VAR TopPPracticos =
TOPN(
TopNValue
,ALLSELECTED( Empleado)
,Empleado[Mov]
)


VAR TopNManiobras =
CALCULATE(
SELECTEDVALUE(Empleado[Mov])
,KEEPFILTERS(TopPPracticos)
,FILTER(Empleado,Empleado[CodigoPractico] <> "X")
)


RETURN

TopNManiobras

 

---------------------

 

The problem is that when using this formula inside the above

VAR CantPract =

CALCULATE(
COUNT(Empleado[CodigoPractico])
)
 
The result is an empty table
 

 

But, when I put a number instead the formula a result is shown, for example

 

VAR CantPract = 14

 

 

Why is that? How could I do a correct measure depending of the amount of employees?

 

This is the empleado's table in case need it

 

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi Antonio_Gomez ,

    According to your code, I create a sample and test. If you put the CantPract part in the RETURN field, you will find it take each row into consideration instead of the whole table. 

    Modify it to:

    TopNPracticos =
VAR CantPract =
    CALCULATE ( COUNT ( Empleado[CodigoPractico] ), ALL ( 'Empleado' ) )
VAR TopNValue =
    ROUNDUP ( CantPract / 2, 0 )
VAR TopPPracticos =
    TOPN ( TopNValue, ALLSELECTED ( Empleado ), Empleado[Mov] )
VAR TopNManiobras =
    CALCULATE (
        SELECTEDVALUE ( Empleado[Mov] ),
        KEEPFILTERS ( TopPPracticos ),
        FILTER ( Empleado, Empleado[CodigoPractico] <> "X" )
    )
RETURN
    TopNManiobras

    Get the correct result:

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

2 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    Hi Antonio_Gomez ,

    According to your code, I create a sample and test. If you put the CantPract part in the RETURN field, you will find it take each row into consideration instead of the whole table. 

    Modify it to:

    TopNPracticos =
VAR CantPract =
    CALCULATE ( COUNT ( Empleado[CodigoPractico] ), ALL ( 'Empleado' ) )
VAR TopNValue =
    ROUNDUP ( CantPract / 2, 0 )
VAR TopPPracticos =
    TOPN ( TopNValue, ALLSELECTED ( Empleado ), Empleado[Mov] )
VAR TopNManiobras =
    CALCULATE (
        SELECTEDVALUE ( Empleado[Mov] ),
        KEEPFILTERS ( TopPPracticos ),
        FILTER ( Empleado, Empleado[CodigoPractico] <> "X" )
    )
RETURN
    TopNManiobras

    Get the correct result:

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

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