Create aggregate table from larger table

[danielhough]

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!

[v-jayw-msft]

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]

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?

[v-jayw-msft · Accepted solution]

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]

Removing this worked! Thanks!!