DAX optimization: weighted average

[Anonymous]

Hi everybody, I have a tricky question about  DAX optimization.

There are two DB

• fact table of sell in data with product EAN CODE. I have NES Sales, CON Sales* and I calculate the NetNet
• fact table of sell out data with product EAN CODE. I have Value Sales, Unit Sales and I calculate the Price

(NES means value sales, CON means quantity in our "company internal language")

I need to perform the calculation of NetNet (and also the Price) at SubBrand Level using a average of single EAN's NetPrice weighetd by Unit Sales.

There is the possibility that for some EAN I have the NetPrice and not the SellPrice, or viceversa.
In other words: I need to consider for the weighted avg only the EAN code that are in both the fact table with valid values.

I wrote this measure, with a VAR to calculate the weight and then perform the calculation

NetNet weighted:=

 VAR UnitSalesYtdWNetNet =
        CALCULATE (
            [Unit sales Ytd],
            FILTER (
                VALUES ( Product_hierarchy_EAN[EAN code for match] ),
                [NES Ytd-1] > 0 && [CON Ytd-1]>0
                    && [Unit sales Ytd] > 0
            )
        )
    
    RETURN
        DIVIDE (
            SUMX (
                ADDCOLUMNS (
                    FILTER (
                        VALUES ( Product_hierarchy_EAN[EAN code for match] ),
                        [CON Ytd-1] > 0
                            && [Unit Sales Ytd] > 0
                    ),
                    "Test", ( [NetNet Ytd-1] ) * [Unit sales Ytd]
                ),
                [Test]
            ),
            UnitSalesYtdWNetNet
        )
)

I need to perform this calculation at Ytd level, with a complication that I can explain with an example: if I select october the Ytd of Unit Sales must be performed as jan to oct; the ytd of NetNet must be performend as jan to sept.

The formula above shows a bad result in term of performance:

and also a couple of CallBackDataID.

 I found an intersting article on SQLBI but I don't understant how to apply the same logic to my case

Any help?

[v-chuncz-msft]

@Anonymous,

You may take a look at the following article.

https://www.sqlbi.com/articles/divide-performance/

[Anonymous]

Thanks @v-chuncz-msft, I will read the article.

By the way, I posted the same question to the Milan PUG forum (sorry is in italian but the question is the same).

Marco Russo replied with a different formula that (of course) is better and faster then mine

NetNet weighted :=
VAR FilterYtd =
    DATESYTD ( 'Date'[Date] )
VAR FilterYtdLY =
    DATEADD ( FilterYtd, -1, YEAR )
VAR EanCodesYtd =
    CALCULATETABLE (
        VALUES ( Product_hierarchy_EAN[EAN code for match] ),
        FilterYtd
    )
VAR EanCodesYtdLY =
    CALCULATETABLE (
        VALUES ( Product_hierarchy_EAN[EAN code for match] ),
        FilterYtdLY
    )
VAR EanFilter =
    INTERSECT ( EanCodesYtd, EanCodesYtdLY )
VAR Numerator =
    SUMX ( EanFilter, [Unit sales Ytd] * [NetNet Ytd-1] )
VAR Denominator =
    CALCULATE ( [Unit sales Ytd], EanFilter )
VAR Result =
    DIVIDE ( Numerator, Denominator )
RETURN
    Result​

Here the logic is:

• create due variables one for Ytd and the other fot Ytd-1
• pre-filter the EAN code using the two variables generating two distinct tables
• create a table with EAN in common in both the tables created before
• run the calculation against these tables and obtain the final result

This works very vell (~70ms of execution w/o any CallBackDataID) but is not exactly the same result of my formula.

I digged deeper and understood that Marco's formula is not filter the EAN code checking the [Unit Sales Ytd]>0 AND [Net Net Ytd-1] >0.

Now i'm trying to use the same formula adding this two condition, without success so far.

If you have any suggestion it will be welcome 🙂

[Anonymous · Accepted solution]

With the help of Marco Russo I finally arrived to the final formula.

This formula consider also the [unit Sales ytd]>0 AND the [Net Net Ytd-1]>0.

The key is filter everything we need before the calculation is done, to avoid Context transiction.

   VAR FilterYtd =
        DATESYTD ( 'datatable'[Data] )
    VAR FilterYtdLM =
        DATEADD ( DATESYTD ( 'datatable'[Data] ), -1, MONTH )
    VAR EanCodesYtd =
        CALCULATETABLE (
            SUMMARIZE ( SELLOUTdata, Product_hierarchy_EAN[EAN code for match] ),
            FilterYtd,
            SELLOUTdata[Measures] = "Unit Sales",
            SELLOUTdata[Valore] > 0
        )
	VAR EanCodesYtdLyLM =
        CALCULATETABLE (
            SUMMARIZE ( SELLINdata, Product_hierarchy_EAN[EAN code for match] ),
            FilterYtdLM,
            SELLINdata[attributo] = "3 Net External Sales" || SELLINdata[attributo] = "Qty in CON",
            SELLINdata[Valore] > 0
    	)
   VAR EanFilter =
    	INTERSECT(EanCodesYtd, EanCodesYtdLyLM)
    
   VAR Numerator = 
   SUMX(EanFilter, [Unit sales Ytd]*DIVIDE([NES Ytd-1],[CON Ytd-1]))
   
   VAR Denominator = 
   CALCULATE([Unit sales Ytd], EanFilter)
   VAR Result = 
   DIVIDE(Numerator, Denominator)
   RETURN
   Result

This formula runs in ~70ms as avg. I applied the same logic to the other measure and finally my report gain a lot in term of speed.

Thanks to Marco Russo for his support