Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AMJG851126
New Member

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
TomMartens
Super User
Super User

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

4 REPLIES 4
TomMartens
Super User
Super User

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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 

Hey Antoine,

 

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

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Great, thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.