cancel
Showing results for
Did you mean:
Resolver I

## SUMX Too Slow - Solution is SUMMARIZE

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

2 REPLIES 2
Community Support

Hi @Laz ,

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Resolver I

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

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.