Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
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
Great, thanks!
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |