Need Help - Measure (Basic)

[alesmarcioni]

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!

[OwenAuger · Accepted 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

[alesmarcioni]

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.