SUMX Too Slow - Solution is SUMMARIZE

[Anonymous]

Hi Community,

I need some assistance in undertanding why my SUMX is so slow performing and why the solution to my problem is SUMMARIZE.

To give you an overview of the solution i am working on:

I want to be able to view the

• Sales £
• Discount £,
• Discount % = Discount £ / Sales £
• Target % = AVERAGE(Taget %)
• vs Target % = (Discount % - Target %) / Target %
• Pottential Savings = sumx(RETAIL_SALES,(RETAIL_SALES[Discount %] - Disc_Target[Target %]) * RETAIL_SALES[Ticket Price])

The model is the below:

So what is the issue i am experiencing?

The measure:

Pottential Savings = sumx(RETAIL_SALES,(RETAIL_SALES[Discount %] - Disc_Target[Target %]) * RETAIL_SALES[Ticket Price])

is returning the following error:

When publishing my solution to the Power BI Service. 

I done much reading into this and understood that SUMX steos through the data one row at a time and is less efficient. What exactly does this mean with the above DAX Formula i put together, can anyone please explain this in their own words?

My understanding is this: the SUMX is trying to caclulate the formula at the lowest granularity for the specified Table which is 'Retail_Sales', hence it returns the max memory consumption error. If i am wrong please do let me know.

What solution have i found?

Part 1 is the below DAX Formula:

Manual Disc % - Target % = 
CALCULATE(

    ([Discount %]-Disc_Target[Target %]),
	        SUMMARIZE(
		        RETAIL_SALES,
		        STORES[Store Group],STORES[Store Name] 
	        )
)    

Part 2 is the below Dax Formula:

Ticket Price = 
CALCULATE(

    SUM(RETAIL_SALES[Sales]),
	        SUMMARIZE(
		        RETAIL_SALES,
		        STORES[Store Group],STORES[Store Name] 
	        )
)    

Part 3 is the below DAX Formula which consists of the above:

Pottential Savings V2 = 
SUMX(VALUES(STORES[Store Group]),
        [Manual Disc % - Target %]*[Ticket Price]
)

After spending multiple hours on the above, can  someone please tell me why: the above 'Pottential Savings V2' is working brilliantly, whilst the SUMX equivalent is not performing at all.

Thanks,

Laz

[v-frfei-msft]

Hi @Anonymous ,

How did you create your visual? Kindly share your pbix to me. Please upload your files to One Drive and share the link here.

Regards,

Frank

[Anonymous]

Hi @v-frfei-msft ,

All my data is sesnisitve. hence quite hesitant to share it. I know this makes it harder for anyone to help me out when i share a post like this.

In regards to how did i create the visual. If understood correctly, all i am using is a Matrix visual which i include the measure 'Pottential Savings' as a value. This measure includes the SUMX.

I am making the assumption that the SUMX measure is displaying this error message, as it goes to the lowest level detail and conducts the calculation row by row. Whilst, when i am using the alternative which is working, with the SUMMARIZE, i stay at a much higher level hence returns the needed result.

Thanks,

Laz