Forum Discussion

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

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

sample.pbix65 KB

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

esgaranel
Frequent Visitor
3 years ago
Ok, we can work with this dataset.

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

Thanks
- Anonymous
  Not applicable
  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
  sample.pbix65 KB