DAX for Summation the Multiplication result of each type separately

[alexyap]

RAW DATA

 

Data Formulation Breakdown

EXPECTED RESULT

Dear All,

I have the RAW data as shown above. I'm trying to create a new measure by using DAX.

Basically I have both distinct types "BUY" and "SELL" for the CCY "EURUSD" and I'm trying to calculate the BUY Average price and SELL Average price. However, I always have the problem that Power BI will aggregate them regardless is "BUY" or "SELL" type.

I also have provided the expected final result that I hope to achieve.

Hoping that someone can shed some light on how should I formulate the DAX for the above.

Regards,

Alex

[sdjensen · Accepted solution]

As long as you always filter your result by either having TYPE in your visual or as a filter it should be 2 fairly basic calculations.

Notional Value = SUMX(Tabel1, Tabel1[PRICE] * Tabel1[VOLUME] )

Avg Px = DIVIDE([Notional Value], SUM(Tabel1[VOLUME]))

 

edit: I know it doesn't make sence to have PRICE in the table.

[Greg_Deckler]

You could create a calculated column or measure:

MyCalc = IF([TYPE]="BUY",CALCULATE(),CALCULATE())

Basically, if tye [TYPE] is "BUY", do one calculation, otherwise, do a different calculation.