Forum Discussion

danielhough's avatar
danielhough
Helper II
6 years ago
Solved

Create aggregate table from larger table

Hey, 

 

I have a table with 100K+ rows and I need to create an aggregated table from this larger table. The aggregated table will have 10 or so columns and a values column. I've tried SUMMARIZE but the new table has blanks in rows and a summarizes the values column at the bottom. I need to create this aggregated table to feed into the custom add-in Arria, since BI has a 30K row limit for custom add-in.

 

Is this possible?

Thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi danielhough ,

     

    I'm not sure if the result was caused by ROLLUP() function since there's no any sample data.

    The addition of the ROLLUP() syntax modifies the behavior of the SUMMARIZE function by adding roll-up rows to the result on the groupBy_columnName columns.

    Please refer to the document below and check if the ROLLUP() function effect the result in your scenario.

    https://docs.microsoft.com/en-us/dax/summarize-function-dax.

     

    Best Regards,

    Jay

    Community Support Team _ Jay Wang

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi danielhough ，


    I'm not sure what you mean about "the new table has blanks in rows and a summarizes the values column at the bottom".

    Could you please share some sample data and expected result to me if you don't have any Confidential Information?

     

    Best Regards,

    Jay

    Community Support Team _ Jay Wang

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

    • danielhough's avatar
      danielhough
      Helper II
      6 years ago

      Hello Jay, it may be hard to share data, so maybe I can start with the code Im using:

       

      Summary HFM Table = SUMMARIZE('HFM_Data_QTD(5 quarters)_102319',
      ROLLUP(ROLLUPGROUP('HFM_Data_QTD(5 quarters)_102319'[Date].[Year],
      'HFM_Data_QTD(5 quarters)_102319'[Date].[Quarter],
      'HFM_Data_QTD(5 quarters)_102319'[Date].[Month],
      'HFM_Data_QTD(5 quarters)_102319'[Date],
      'HFM_Data_QTD(5 quarters)_102319'[LOWEST_LEVEL_DESC_TX],
      'HFM_Data_QTD(5 quarters)_102319'[LEVEL6_DESC_TX],
      'HFM_Data_QTD(5 quarters)_102319'[LEVEL5_DESC_TX],
      'HFM_Data_QTD(5 quarters)_102319'[LEVEL4_DESC_TX],
      'HFM_Data_QTD(5 quarters)_102319'[LEVEL3_DESC_TX],
      'HFM_Data_QTD(5 quarters)_102319'[LEVEL2_DESC_TX],
      'HFM_Data_QTD(5 quarters)_102319'[LEVEL1_DESC_TX],
      'HFM_Data_QTD(5 quarters)_102319'[ENT_BUS_UNIT_NM])),
      "YTD_USD_NET_AMT",sum('HFM_Data_QTD(5 quarters)_102319'[YTD_USD_NET_AMT]))
       
      The issues I have wtih this code is, I have a series of blanks cells in each column and a total at the bottom of the YTD column. 
       
      Does this help?
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi danielhough ,

         

        I'm not sure if the result was caused by ROLLUP() function since there's no any sample data.

        The addition of the ROLLUP() syntax modifies the behavior of the SUMMARIZE function by adding roll-up rows to the result on the groupBy_columnName columns.

        Please refer to the document below and check if the ROLLUP() function effect the result in your scenario.

        https://docs.microsoft.com/en-us/dax/summarize-function-dax.

         

        Best Regards,

        Jay

        Community Support Team _ Jay Wang

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