Optimize speed of SUMx and Share of category

[RTERCERO]

Hello,

I'm calculating SKU adherence which considers evaluating row by Row, the calculation works properly when switching time frames however as I add rows to the model It becomes a lot slower, here are the mathematical Formulas with their measures:

SKU Adherence Measure = 
VAR t_ =
    ADDCOLUMNS ( SKU_TEST, "a_", [% Adherence], "b_", [Participation] )
RETURN
    SUMX ( t_, [a_] * [b_] )

Where: 

Adherence = 
IF( 
    DIVIDE([Real Amount],[Planned Amount])>1,
    1,
DIVIDE([Real Amount],[Planned Amount]))

Planned Amount = 
If(CALCULATE(SUMX(SKU_TEST,SKU_TEST[PLAN]),DATESYTD(SKU_TEST[Date]))=0,
    BLANK(),
CALCULATE(SUMx(VALUES(SKU_TEST[PLAN]),Calculate(SUM(SKU_TEST[PLAN]),DATESYTD(SKU_TEST[Date]))))
)

Real Amount = 
If(CALCULATE(SUMX(SKU_TEST,SKU_TEST[PLAN]),DATESYTD(SKU_TEST[Date]))=0,
    BLANK(),
CALCULATE(SUMx(VALUES(SKU_TEST[REAL]),Calculate(SUM(SKU_TEST[REAL]),DATESYTD(SKU_TEST[Date]))))
)

Participation = 
If(
   [Planned Amount]=0,
    BLANK(),
DIVIDE([Planned Amount],[Total Plan of Selected Period]))

Total Plan of Selected Period = 
If(
   [Planned Amount]=0,
    BLANK(),
CALCULATE([Planned Amount] ,ALLSELECTED(SKU_TEST)))

First inquire is , how do I make it Faster?

Second inquire is related to "Participation", it should calculate based on "Product"  an be scalable to  into any other category as "Program" or "Area", Currently its not working like that, So I tried an alternate formula named "Participation 2" That calculates properly bus when used to calculate SKU adherence (SKU Adherence 2 for example on model) runs out of memory, also I would need to make a measure for each category:

SKU Adherence Measure 2 = 
VAR t_ =
    ADDCOLUMNS ( SKU_TEST, "a_", [% Adherence], "b_", [Participation 2] )
RETURN
    SUMX ( t_, [a_] * [b_] )

Participation 2 = 
If(
   [Planned Amount]=0,
    BLANK(),
DIVIDE([Planned Amount],[Total Plan of Selected Period 2])
)

Total Plan of Selected Period 2 = 
If(
   [Planned Amount]=0,
    BLANK(),
CALCULATE([Planned Amount] ,ALLSELECTED(SKU_TEST),SKU_TEST[PROGRAM]=min(SKU_TEST[PROGRAM])))

Attaching File Samples:https://we.tl/t-to7V3wpZpV

Best Regards

RT

[V-lianl-msft · Accepted solution]

Hi @RTERCERO ,

Unfortunately, due to the performance of my computer, opening the pbix you provided will get stuck, so I can't check your pbix.

In general, avoid bi-directional relations at all costs.

https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/ 

What you need to know about the if statement is:

Incorrect DAX: 

Participation 2 = 
If(
   [Planned Amount]=0,
    BLANK(),
DIVIDE([Planned Amount],[Total Plan of Selected Period 2])
)

Here, measures are calculated continuously, meaning the [Total Rows] expression is calculated twice: first for the condition check, then for the true condition expression.

Correct DAX: 

Participation 2 = 
var Planned Amount_ = [Planned Amount]
var Total Plan of Selected Period 2_ = [Total Plan of Selected Period 2]
If(
   Planned Amount_=0,
    BLANK(),
DIVIDE(Planned Amount_,Total Plan of Selected Period 2_)
)

Instead of calculating the same expression multiple times, you can store the resulting measure value in a variable. You can use a variable reference wherever required. The same variable process applies to all instances where you call the same measure. Variables can help you avoid repetitive functions.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.