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
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
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!
Highlighted
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
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 357 members 3,491 guests
Please welcome our newest community members: