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

[Anonymous]

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 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). 

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.

 Model- 

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

Here's my setup- 

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

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

[Anonymous]

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...?

[Anonymous]

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. 

[Anonymous · Accepted solution]

"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?

[Anonymous]

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! 

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

[Anonymous]

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

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