Forum Discussion

esgaranel's avatar
esgaranel
Frequent Visitor
3 years ago
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

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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