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 fo...
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.

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.