Forum Discussion

Rinn's avatar
Rinn
Frequent Visitor
2 years ago
Solved

Optimized DAX (SUMX and SUMMARIZE)

Hi,

I am trying to optimize my DAX measure because it is too slow on Service BI...

 

To sum up, I am trying to calculate the total sales (%) for each customized category (A, B etc...) of my products.

Total sales Category A % =
VAR apps =
    ADDCOLUMNS (
        SUMMARIZE (
            'Products',
            'Products'[Category],
            "@Rows", SUM ( 'Products Sales'[Sales Unit] )
        ),
        "@InScope", [Created Category Measure]
    )
RETURN
    SUMX ( FILTER ( apps, [@InScope] = A ), [@Rows] )/SUM ( 'Products Sales'[Sales Unit] )
 
('Products' and 'Products Sales' tables have a ONE to ONE and bi-directionnel relationship, I know it doesn't help my case but I can't changed it... )
 
The SUMX and SUMMARIZE seem to slow down my performance but I don't know how to optimize it with the measures and the context...
Can you help me pls ? I'm still a beginner, I'm trying to learn 🙂


  • sjoerdvn's avatar
    sjoerdvn
    2 years ago

    No need for a SUMX I think, try this:

    Total sales Category A % = 
CALCULATE(SUM ( 'Products Sales'[Sales Unit] ), FILTER(VALUES( 'Products'[Category]), [Created Category Measure] = A))
/ SUM ( 'Products Sales'[Sales Unit] )

1 Reply

  • sjoerdvn's avatar
    sjoerdvn
    Solution Sage
    2 years ago

    No need for a SUMX I think, try this:

    Total sales Category A % = 
CALCULATE(SUM ( 'Products Sales'[Sales Unit] ), FILTER(VALUES( 'Products'[Category]), [Created Category Measure] = A))
/ SUM ( 'Products Sales'[Sales Unit] )