Forum Discussion

reynaldo_malave's avatar
reynaldo_malave
Helper III
2 years ago

Return TopN by category

Hi guys,

 

Am trying to build a top 2 Supervisors by company division. The goal is to use this information to highlight these supervisors within a matrix visual. Especifically I am trying to return a 1 or blank like the table below. Imagine Sales is a measure [Sales]

 

DivisionNameSalesBottom2
North AmericaJames1001
North America

Rick

1501
Europecharles2001
EuropeLouise3001
EuropeVanessa450 
North AmericaZara350 
Norht AmericaDarling275 
EuropeDagger310 

 

so far i have the following dax code (not my best work) since i could not return a single table from a calculation. I build a table for each division and then use union. 

 

the problem i am facing has to do with filter context. When i use the measure in the visual it return a 1 for all supervisors instead the ones on the list of the union.

 

here is the code i am using

 

RankAsA2 = 

VAR RankedSupervisorsFranquicias =
    
CALCULATETABLE(    
    ADDCOLUMNS(
        SUMMARIZE('Store'; 'Store'[Supervisor]; 'Store'[Division]);
        "_AsA"; [% $ AsA]
    );
    'Store'[Division] = "FRA"
)

VAR RankedSupervisorsSucursales =

CALCULATETABLE(
    ADDCOLUMNS(
        SUMMARIZE('Store'; 'Store'[Supervisor]; 'Store'[Division]);
        "_AsA"; [% $ AsA]
    );
    'Store'[Division] = "SUC"
)

VAR Top2Franquicias =

SUMMARIZE(
    TOPN(
        2;
        FILTER(RankedSupervisorsFranquicias; [_AsA] <> BLANK());
        [_AsA];
        ASC
    );
    'Store'[Supervisor]
)

VAR Top2Sucursales =

SUMMARIZE(
    TOPN(
        2;
        FILTER(RankedSupervisorsSucursales; [_AsA] <> BLANK());
        [_AsA];
        ASC
    );
    'Store'[Supervisor]
)


VAR UnionTablasTop2 = UNION( Top2Franquicias; Top2Sucursales )

VAR SupervisorSeleccionado = SELECTEDVALUE( 'Store'[Supervisor] )

Return 
    IF(
        SUMX(
            FILTER(
                UnionTablasTop2;
                'Store'[Supervisor] = SupervisorSeleccionado
            );
            1
        ) > 0;
        1;
        BLANK()
    )

 

 

Can anyone help me out with this calculation.

 

Thanks

1 Reply