Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everybody, I'm Alessandro, a new user in this forum. It is really nice to meet you!
I started using Power B.I. just this week and, !wow!, it is a really great tool!
I need a (i think basic) help for create a new measure because i don't understand how to make a simple multiplication.
I've got 2 tables (which have a join on ItemID).
TABLE A: ItemID, cost
TABLE B: SerialNumber(univ.), ItemID, Qty
For every SerialNumber i have to make (TABLE A).cost * (TABLE B).Qty
In SQL it is an easy operation, but i can't get this in Power BI.
Can anyone explain me any step to make this measure?
Thank you so mutch for the help!
Solved! Go to Solution.
Hi Alessandro,
Welcome to the forum 🙂
In this situation, you can use SUMX to iterate over the rows of 'TABLE A' and multiply each row's cost by the sum of Qty for the corresponding rows in 'TABLE B'.
In fact you can iterate over the distinct values of 'TABLE A'[cost] rather than each row of 'TABLE A':
= SUMX ( VALUES ( 'TABLE A'[cost] ), 'TABLE A'[cost] * CALCULATE ( SUM ( 'TABLE B'[Qty] ) ) )
Does a measure like this give you the result you expect?
Regards,
Owen
Hi Alessandro,
Welcome to the forum 🙂
In this situation, you can use SUMX to iterate over the rows of 'TABLE A' and multiply each row's cost by the sum of Qty for the corresponding rows in 'TABLE B'.
In fact you can iterate over the distinct values of 'TABLE A'[cost] rather than each row of 'TABLE A':
= SUMX ( VALUES ( 'TABLE A'[cost] ), 'TABLE A'[cost] * CALCULATE ( SUM ( 'TABLE B'[Qty] ) ) )
Does a measure like this give you the result you expect?
Regards,
Owen
Owen, the result is exactly what i expected.
Great, and thankyou so mutch!
Hope I'll become more skilled in this sense
Thank you again!
Alessandro M.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |