Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Creating a Summarized Table by Top N

Hi,

 

I'm trying to create a summarized table filtered on the top (most recent) 12 months in the original table.  It would need to be filtered/summarized by the "year_period" field which is in a format like "202111" shown below.

 

 

Any help accomplishing this would be much appreciated.  Thanks!

 

Jordan

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    I create a sample to show you how to create a summarized TopN month table by Dax.

    Try this code to create a calcualted table.

     

    Summarized Table by Top N =
VAR _N = 12
VAR _SUMMARIZED =
    SUMMARIZE ( 'Table', 'Table'[year_period] )
VAR _ADDRANK =
    ADDCOLUMNS ( _SUMMARIZED, "RANK", RANKX ( _SUMMARIZED, [year_period],, DESC ) )
VAR _TopN =
    SUMMARIZE ( FILTER ( _ADDRANK, [RANK] <= _N ), [year_period] )
RETURN
    _TopN

     

    Result is as below.

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous ,

     

    Create a new column in the table

    Year month = format([Date], "YYYYMM")

     

    Create a new table

    summarize(Table, Table[Month Year], "Measure", sum(Table[Value] ) )

     

     

    Add other per need

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks amitchandak  but I'm not sure how this would summarize the new table by Top 12 months in original table?

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous ,

        I create a sample to show you how to create a summarized TopN month table by Dax.

        Try this code to create a calcualted table.

         

        Summarized Table by Top N =
VAR _N = 12
VAR _SUMMARIZED =
    SUMMARIZE ( 'Table', 'Table'[year_period] )
VAR _ADDRANK =
    ADDCOLUMNS ( _SUMMARIZED, "RANK", RANKX ( _SUMMARIZED, [year_period],, DESC ) )
VAR _TopN =
    SUMMARIZE ( FILTER ( _ADDRANK, [RANK] <= _N ), [year_period] )
RETURN
    _TopN

         

        Result is as below.

        Best Regards,
        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.