SUMMARIZECOLUMNS not able to process huge data size.

[NidhiBhusari]

Hi,

I have created a custom table using SUMMARIZECOLUMNS function:-

SUMMARIZECOLUMNS(BI_SALESFACT[PDID],BI_CUSTOMER[FIELD_GROUP_TYPE],BI_CUSTOMER[F_LEVEL4_DESC],BI_CUSTOMER[Entity],BI_CUSTOMER[CUSTOMER_PRACTICE_NAME],BI_CUSTOMER[BillToCustomer_Name],"Sales",SUM(BI_SALESFACT[NET_SALES]),"GP",(SUM(BI_SALESFACT[NET_SALES]) - SUM(BI_SALESFACT[COMM_EXTENDED_COST_AMT])))

Further created calculations like Cummulative% using this table. When I am using Entity and Cummulative% in grid then it keeps loading and after a certain point of time it gives error.

Tried with limited duration (1month) of data in Desktop and after publishing to PBI service increased the Data size using parameters & refreshed still getting same error.

Any kind of help will be appreciated.

Thanks & Regards,
Nidhi

[amitchandak]

@NidhiBhusari , can share Cummulative calculation

it should be like

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

or

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

[NidhiBhusari]

Hi @amitchandak ,

first, I have calculated GP_MTD and then used it to calculate % of total followed by Cumulative percent.

GP_MTD =

VAR selectedmonth =

SELECTEDVALUE (disconnected_BI_Period[FISC_MONTH_ID])

VAR result =

CALCULATE ( [SUmGP], BI_Period[FISC_MONTH_ID] = selectedmonth )

RETURN

result

% of Total(GP) = [GP_MTD]/CALCULATE([GP_MTD],ALL('Rank Analysis'))

Cummulative Percent(GP) =

var a= CALCULATE (

[% of Total(GP)],

FILTER ( ALL ('Rank Analysis'), 'Rank Analysis'[F_LEVEL4_DESC] <= MAX ('Rank Analysis'[F_LEVEL4_DESC])))

var b= CALCULATE (

[% of Total(GP)],

FILTER (all('Rank Analysis'),'Rank Analysis'[Entity] <= MAX ('Rank Analysis'[Entity])))

var c = CALCULATE (

[% of Total(GP)],

FILTER (all('Rank Analysis'),'Rank Analysis'[CUSTOMER_PRACTICE_NAME] <= MAX ('Rank Analysis'[CUSTOMER_PRACTICE_NAME])))

var d = CALCULATE (

[% of Total(GP)],

FILTER (all('Rank Analysis'),'Rank Analysis'[BillToCustomer_Name] <= MAX ('Rank Analysis'[BillToCustomer_Name])))

return

IF(ISINSCOPE('Rank Analysis'[BillToCustomer_Name]),d,

IF(ISINSCOPE('Rank Analysis'[CUSTOMER_PRACTICE_NAME]),c,

IF(ISINSCOPE('Rank Analysis'[Entity]),b,a)))

[amitchandak]

@NidhiBhusari , Try like

Switch( True(),

ISINSCOPE('Rank Analysis'[BillToCustomer_Name]),CALCULATE (
[% of Total(GP)],
FILTER (all('Rank Analysis'),'Rank Analysis'[BillToCustomer_Name] <= MAX ('Rank Analysis'[BillToCustomer_Name]))),

ISINSCOPE('Rank Analysis'[CUSTOMER_PRACTICE_NAME]) , CALCULATE (
[% of Total(GP)],
FILTER (all('Rank Analysis'),'Rank Analysis'[CUSTOMER_PRACTICE_NAME] <= MAX ('Rank Analysis'[CUSTOMER_PRACTICE_NAME]))) ,

ISINSCOPE('Rank Analysis'[Entity]) ,CALCULATE (
[% of Total(GP)],
FILTER (all('Rank Analysis'),'Rank Analysis'[Entity] <= MAX ('Rank Analysis'[Entity]))) ,

CALCULATE (
[% of Total(GP)],
FILTER ( ALL ('Rank Analysis'), 'Rank Analysis'[F_LEVEL4_DESC] <= MAX ('Rank Analysis'[F_LEVEL4_DESC])))

)

[NidhiBhusari]

Hello @amitchandak ,

Tried the measure you have shared still it is not working and giving the same error as above.
Is there any other way we can optimize the DAX?