cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AMJG851126 Frequent Visitor
Frequent Visitor

Multiplying rows across tables as a measure

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Multiplying rows across tables as a measure

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:

image.png

Hopefully this is what you are looking for.

 

Regards,
Tom

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
4 REPLIES 4
Highlighted
Super User
Super User

Re: Multiplying rows across tables as a measure

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:

image.png

Hopefully this is what you are looking for.

 

Regards,
Tom

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
AMJG851126 Frequent Visitor
Frequent Visitor

Re: Multiplying rows across tables as a measure

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 

Super User
Super User

Re: Multiplying rows across tables as a measure

Hey Antoine,

 

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

 

Regards,

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
AMJG851126 Frequent Visitor
Frequent Visitor

Re: Multiplying rows across tables as a measure

Great, thanks!

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 93 members 1,629 guests
Please welcome our newest community members: