Forum Discussion
Solved
esgaranel
How can I create a table (not a visual table) that shows the top two grades per day. I tried it with functions TOPN and RANK without success. Thanks
HI esgaranel,
You can take a look at the following calculate table formula if it suitable for your requirement:
NewTable = VAR summary = ADDCOLUMNS ( SUMMARIZE ( Hoja1, [FECHA], [CENTRO], "C_NOTA", COUNT ( Hoja1[NOTA] ) ), "C_NOTA&RAND", [C_NOTA] + RAND () ) RETURN FILTER ( ADDCOLUMNS ( summary, "Rank", RANKX ( FILTER ( summary, [FECHA] = EARLIER ( Hoja1[FECHA] ) ), [C_NOTA&RAND], , DESC, SKIP ) ), [Rank] <= 2 )Notice: my formula will count the 'NOTA' field based on the 'FECHA', 'CENTRO' fields group, I also added RAND function to handle the ranking on these records who has the same 'NOTA' count.
Regards,
Xiaoxin Sheng
Ok, we can work with this dataset.
Thanks
HI esgaranel,
You can take a look at the following calculate table formula if it suitable for your requirement:
NewTable =
VAR summary =
ADDCOLUMNS (
SUMMARIZE ( Hoja1, [FECHA], [CENTRO], "C_NOTA", COUNT ( Hoja1[NOTA] ) ),
"C_NOTA&RAND", [C_NOTA] + RAND ()
)
RETURN
FILTER (
ADDCOLUMNS (
summary,
"Rank",
RANKX (
FILTER ( summary, [FECHA] = EARLIER ( Hoja1[FECHA] ) ),
[C_NOTA&RAND],
,
DESC,
SKIP
)
),
[Rank] <= 2
)
Notice: my formula will count the 'NOTA' field based on the 'FECHA', 'CENTRO' fields group, I also added RAND function to handle the ranking on these records who has the same 'NOTA' count.
Regards,
Xiaoxin Sheng