Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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
Hi @esgaranel,
You can create new calculated table with variable table to summary record and add custom field to get correspond ranking, then filter on the variable table to keep top n rank records based on each group.
If you are confused about coding formula, can you please share some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
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
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |