Multiplying rows across tables as a measure

[AMJG851126]

Hi there

I am new to Power BI and am struggling to create an excel sumproduct powerbi equivalent.

I have two tables, one named Dates and the other Financials. A simplified version of the tables is below

Dates

YearMonth           YTD%

201801                 53%

201802                 47%

Financials

YearMonth           BalanceType          Value

201801                 Credit Balance        100

201801                 Debit Balance          50

201802                 Credit Balance         150

201802                 Debit Balance          40

I have created a relationship based on YearMonth.

I am struggling to create a measure that will multiply the value with the YTD% and then return the sum based on BalanceType. It seems to first add all Valu and then multiply with the sum of the YTD%. Any ideas how I could achieve this?

Thanks

[TomMartens · Accepted solution]

Hey,

I guess this will work, make sure you use the YearMonth column from the Dates table in all of your visuals:

Measure = 
SUMX(
    'Financials'
    , [Value] * RELATED(Dates[YTD%])
)

Using this measure and your sample data I can create this table:

Hopefully this is what you are looking for.

Regards,
Tom

[AMJG851126]

Hi Tom

Thank you for your solution, it has really unlocked a lot for me. 

The idea was that i could not have the YearMonth in all my visuals because I mostly report yearly figures. It does however seem to work without it. Please let me know if there is something i should look out for.

Thank you!

Antoine 

[TomMartens]

Hey Antoine,

as long as the year column is from the same table as the yearmonth, it will work flawless.

Regards,

Tom

[AMJG851126]

Great, thanks!