Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |