Forum Discussion
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]
| Division | Name | Sales | Bottom2 |
| North America | James | 100 | 1 |
| North America | Rick | 150 | 1 |
| Europe | charles | 200 | 1 |
| Europe | Louise | 300 | 1 |
| Europe | Vanessa | 450 | |
| North America | Zara | 350 | |
| Norht America | Darling | 275 | |
| Europe | Dagger | 310 |
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
- Ashish_MathurSuper User
Hi,
PBI file attahced. You may now filter the rank measure with the criteria of <=2.
Hope this helps.