cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fonz11 Regular Visitor
Regular Visitor

Help aggregate HOURLY PnL figures for multiple days and multiple zones (pbix included)

 

Hi, 

 

I've made my best attempt to SUMX my data to get an accurate Profit and Loss (PnL) figure but I have been unsuccessful Robot Mad in writing a single measure that calculates the correct total amount when selecting multipule days and multipule zones. 

 

I think my main problem is that my fact tables are at the hourly level and my calendar is daily. I have a seperate dim table for Hours (HE). I also am trying to slice and dice by zone (there are 5). Robot Frustrated

 

I tried using a SUMX for the hours and then a SUMX on the SUMX for the Zones and that fixed it for a single day…but when I select more than one day the sum skyrockets again. I’m sure there is a formula that incorporates additional Filters and Date logic but I haven’t been able to figure out the formula pattern L. Hoping someone can help with some syntax or point me in the right direction in the form of a specific example having to do with hourly calendar aggregations.Robot Very Happy

 

 Model- 

https://www.dropbox.com/s/31tcais3wqmmtt3/SAMPLE2.pbix?dl=0

 

Here's my setup- 

 

HelpMe.png

 

 

 

 

 

ASK- (the model has more measure details if it helps)

 

HelpMe2.png

 

 

 

Added together, the total for both days for both zones should be $790.11. Can you please help me write the measure that will calc it? 

 

Thank you, 

 

Fonz

 

1 ACCEPTED SOLUTION

Accepted Solutions
scottsen Senior Member
Senior Member

Re: Help aggregate HOURLY PnL figures for multiple days and multiple zones (pbix included)

"The amount of money made is derived by multiplying the volume by the price spread, Per hour."

 

 

Ya, I don't think that is what you are doing right now.   You are adding everything together and THEN multiplying them.

 

I assume [BIDS DEC MW] * [LMP DA-RT] is valid at some interval... Date+Time I guess?

 

So maybe    SUMX(Date, SUMX(HE, [BIDS DEC MW] * [LMP DA-RT])) would work?

View solution in original post

5 REPLIES 5
Highlighted
scottsen Senior Member
Senior Member

Re: Help aggregate HOURLY PnL figures for multiple days and multiple zones (pbix included)

Your model generally looks good to me, but I'm skeptical of :

 

PnL DEC = [BIDS DEC MW] * [LMP DA-RT]

 

(and the INC version)

 

Just... intuitively... I'm afraid of adding stuff together into some aggregate, then multiplying those sums.

 

It might be something else... but... that's my gut.

 

You want to tell us what PnL DEC is supposed to mean...?

fonz11 Regular Visitor
Regular Visitor

Re: Help aggregate HOURLY PnL figures for multiple days and multiple zones (pbix included)

Thanks for the response. I see your point. 

 

INC and DEC PnL is the dollar amount gained or lost depending on the price spread for that hour. 

 

If I have a positive INC value (i.e. volume) for an hour where the delta between DA and RT is positive, I make money. 

If I have a positive INC value (i.e. volume) for an hour where the delta between DA and RT is negative, I lose money. 

 

If I have a negative DEC value (i.e. volume) for an hour where the delta between DA and RT is positive, I lose money. 

If I have a negative DEC value (i.e. volume) for an hour where the delta between DA and RT is negative, I make money. 

 

The amount of money made is derived by multiplying the volume by the price spread, Per hour. 

 

I didnt think calculating INCS and DECS seperately then adding them together would be problem. 

scottsen Senior Member
Senior Member

Re: Help aggregate HOURLY PnL figures for multiple days and multiple zones (pbix included)

"The amount of money made is derived by multiplying the volume by the price spread, Per hour."

 

 

Ya, I don't think that is what you are doing right now.   You are adding everything together and THEN multiplying them.

 

I assume [BIDS DEC MW] * [LMP DA-RT] is valid at some interval... Date+Time I guess?

 

So maybe    SUMX(Date, SUMX(HE, [BIDS DEC MW] * [LMP DA-RT])) would work?

View solution in original post

fonz11 Regular Visitor
Regular Visitor

Re: Help aggregate HOURLY PnL figures for multiple days and multiple zones (pbix included)

I'm following ya....yes, valid for date + hour

 

I'll try to make that double SUMX work....

fonz11 Regular Visitor
Regular Visitor

Re: Help aggregate HOURLY PnL figures for multiple days and multiple zones (pbix included)

I ended up needing to use THREE SUMX's for my INCs then another THREE for my DECs and adding them together in the same measure...and that got me to my desired result! Robot LOL

 

PnL =((

SUMX(VALUES('CALENDAR'[Date]),
SUMX(VALUES(HE[HE]),
SUMX(VALUES(Zone[Zone (5 min)]),
[BIDS INC MW]*[LMP DA-RT]))))
+
(SUMX(VALUES('CALENDAR'[Date]),
SUMX(VALUES(HE[HE]),
SUMX(VALUES(Zone[Zone (5 min)]),
[BIDS DEC MW]*[LMP DA-RT])))))

 

If someone has a more elegant way to get to the same place I'm all ears! lol. 

 

Scottsen, thanks for helping me walk through this and nudging me to try to multi SUMX in the same measure and performing the multiplication within the same measure as well. I greatly apprecaite it. 

 

--fonz

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 277 members 3,083 guests
Please welcome our newest community members: