cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

DAX HELP Multi Sumx alternative?

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]),

+

(

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

1 ACCEPTED SOLUTION
Super User

@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)] )
+ [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 🙂

Owen Auger
Blog
7 REPLIES 7
Super User

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 🙂

Owen Auger
Blog
Anonymous
Not applicable

@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

Super User

@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)] )
+ [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 🙂

Owen Auger
Blog
Anonymous
Not applicable

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
Not applicable

Okay, THAT is hilarious 🙂

"Uh... you did".

🙂

Anonymous
Not applicable

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
Not applicable

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?

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors