esgaranel

[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

[v-shex-msft]

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

[esgaranel]

Ok, we can work with this dataset.

https://docs.google.com/spreadsheets/d/1s2qb9PpxMeirkV-seVGN0UmlaBQ0nFKw/edit?usp=share_link&ouid=10...

Thanks

[v-shex-msft · Accepted 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