DAX HELP Multi Sumx alternative?

[Anonymous]

Can someone take a look at the measure below and tell me if there is a better way to accomplish what I am attemping with a multi-level "SUMX".? Basically trying to get a feel for if there is an optimized way to express the same measure becasue the measure below performs very slowly. I'm calculating PnL down to the HOUR across multiple Regions and markets and need "the whole to be the sum of the parts". 

PnL = (

SUMX(VALUES('CALENDAR'[DATE]),
SUMX(VALUES(HE[HE]),
SUMX(VALUES(CTRL[CTRL]),
SUMX(VALUES(DataClass[ZONE]),

[INCS MWH]*[DART Spread]+[DEV Cost INCS])))))

+

(

SUMX(VALUES('CALENDAR'[DATE]),
SUMX(VALUES(HE[HE]),
SUMX(VALUES(CTRL[CTRL]),
SUMX(VALUES(DataClass[ZONE]),

[DECS MWH (neg)]*[DART Spread]+[DEV Cost DECS])))))

Thanks for any help, 

Alfonso

[OwenAuger]

Hi Alfonso@Anonymous, @Anonymous, @Anonymous

A pattern I have used in the past instead of nested SUMX is the SUMX ( SUMMARIZE ( ... ), ... ) pattern. I have found it performs better in some models. The key difference from nested SUMXs is that it will ensure you sum over only existing combinations of the dimensions.

This pattern works as long as the dimensions which you are SUMX-ing over are all on the 1-side of a relationship with your fact table.

Also, since your two quad-SUMXs were over the same dimensions, you can get by with one SUMX

The result would be something like this:

PnL =
SUMX (
   SUMMARIZE ( YourFactTable, 'CALENDAR'[DATE], HE[HE], CTRL[CTRL], DataClass[ZONE] ),
   [INCS MWH] * [DART Spread] + [DEV Cost INCS]
   + [DECS MWH (neg)] * [DART Spread] + [DEV Cost DECS]
)

Or, since [DART Spread] appears in both expressions being summed, you could simplify slightly (to avoid evaluating it twice):

PnL =
SUMX (
   SUMMARIZE ( YourFactTable, 'CALENDAR'[DATE], HE[HE], CTRL[CTRL], DataClass[ZONE] ),
   ( [INCS MWH] + [DECS MWH (neg)] ) * [DART Spread]
   + [DEV Cost INCS] + [DEV Cost DECS]
)

Would be interested in whether these perform any better.

Cheers,

Owen 🙂

[Anonymous]

@OwenAuger thanks for taking the time! I'm eager to try this out today. I've never used SUMMERIZE before. 

One wrinkle- does it matter that the compnents to my measures are on 3 different fact tables? They are on the 1-side (I have a FACT tbl for MWH measure, FACT tbl for DART, and FACT tbl for DEV). 

Check out the diagram view that I've annotated for better context- 

THNX AGAIN, 

fonz

[OwenAuger · Accepted solution]

@Anonymous

Thanks for that 🙂

You will have to change the measure if there are multiple fact tables involved.

I have two ideas:

1. Use SUMX with SUMMARIZECOLUMNS. SUMMARIZECOLUMNS doesn't require you to specify the table to be summarized, and if you add a column with an expression, it will automatically remove rows where the expression is blank.
   (Side note: SUMMARIZECOLUMNS didn't previously work within a filter context, but now it appears to work. Interesting article here)
   

   PnL SUMMARIZECOLUMNS =
   SUMX (
       SUMMARIZECOLUMNS (
           'CALENDAR'[DATE],
           HE[HE],
           CTRL[CTRL],
           DataClass[ZONE],
           "ExpressionToSum",
           ( [INCS MWH] + [DECS MWH (neg)] ) * [DART Spread]
               + [DEV Cost INCS]
               + [DEV Cost DECS]
       ),
       [ExpressionToSum]
   )

2. Use the SUMMARIZE method with multiple fact tables by SUMMARIZE-ing each table and take the union.
   

   PnL SUMMARIZE Union =
   VAR BIDS_Summarized =
       SUMMARIZE ( BIDS, 'CALENDAR'[DATE], HE[HE], CTRL[CTRL], DataClass[ZONE] )
   VAR LMP_Summarized =
       SUMMARIZE ( LMP, 'CALENDAR'[DATE], HE[HE], CTRL[CTRL], DataClass[ZONE] )
   VAR DEV_Summarized =
       SUMMARIZE ( LMP, 'CALENDAR'[DATE], HE[HE], CTRL[CTRL], DataClass[ZONE] )
   VAR Union_Summarized =
       DISTINCT ( UNION ( BIDS_Summarized, LMP_Summarized, DEV_Summarized ) )
   RETURN
       SUMX (
           Union_Summarized,
           ( [INCS MWH] + [DECS MWH (neg)] )
               * [DART Spread]
               + [DEV Cost INCS]
               + [DEV Cost DECS]
       )

I think the SUMMARIZECOLUMNS version should perform best, but would be interested in how actual performance turns out.

Cheers,

Owen 🙂

[Anonymous]

Thanks @Anonymous and @Anonymous for the responses. The best I can answer is this was the first pattern I used that acheieved the results I was expecting (again- mainly having to do with the whole being the sum of its parts i.e. sumx). Regualr SUM wasnt getting it done. 

Another wrinkle is that I originally came up with this pattern almost two years ago in excel power pivot. At the time BI and Excel features were more aligned. I don't think that's making a difference in my case though. 

The spirit behind my post was to see if anyone glancing at my "quad" sumx would recogise the pattern and say---"oh...i see what he's trying to do...but he should use "_ _ _ _ _ _" expression instead." 

Also, here's an old post that originally helped me come up with my quad sumx formula- @Anonymous looks like the multi-sumx was your idea to begin with! LOL. I'm just realizing it now...check it out...too funny.

Original Post

Thnx to anyone willing to help 

[Anonymous]

Okay, THAT is hilarious 🙂

"What dumb ass had you write quad-nested sumx?"

"Uh... you did".

🙂

[Anonymous]

I have to question your overall approach.  What are you trying to achieve overall by using this?

Would you be better having a simple Sum measure (not SUMX), then making use of the Power BI Reporting engine to display that data hourly?  Surely if you've linked your tables correctly you should be able to display this data in either a graph or a matrix with the granularity you need.

[Anonymous]

I'm with Ross, you have GOT to be off in the weeds here.

Maybe show us what is inside your the base measure that are used inside the quad-sumx?