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.
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
Solved! Go to Solution.
@Anonymous
Thanks for that 🙂
You will have to change the measure if there are multiple fact tables involved.
I have two ideas:
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] )
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 🙂
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 🙂
@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
@Anonymous
Thanks for that 🙂
You will have to change the measure if there are multiple fact tables involved.
I have two ideas:
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] )
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 🙂
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.
Thnx to anyone willing to help
Okay, THAT is hilarious 🙂
"What dumb ass had you write quad-nested sumx?"
"Uh... you did".
🙂
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.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |