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

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.

      • danielhough's avatar
        danielhough
        Helper II
        6 years ago

        Removing this worked! Thanks!!