DAX formula to make calculation from ODBC connected table...

[unclejemima]

Oh boy...I'm a NOOB at this.

I have a ODBC connected table that I need to create a calculation from.

Its a transaction sales table with columns "quantity" and "price"

I need to multiply the quantity by price...but the OBDC connected table does not have that, only the values above.

I'm assuming to do this I'll need to create a new table in Power BI then create a DAX expression to multiply Quantity by Price...only I've got not clue how to do this.

Would someone mind helping me with the expression?

Thanks!

[dearwatson]

No worries 🙂

Bring the table in and close and load the query.

first you need an explicit measure for both quatity and price. Rule of thumb.. always do this.. dont use auto SUM

Quantity = SUM(Table1[Quantity])

Price = SUM(Table1[Price])

Then you can reference the measure you just made in another measure like this:

Total Cost = [Quantity]*[Price]

drag them all into a table and you should be apples

[mattbrice · Accepted solution]

"drag them all into a table and you should be apples"

Errr....sorry but not quite.  You can't sum up all the different quantities, then sum up all the different prices,  then multiply the two aggregations together.  That will almost assuredly not give you the correct result.  

You need to mutiply row by row:  Line Sales = Quantity * Price, and then sum up the Line Sales amounts:

Total Sales = SUMX ( Table1, Table1[Quantity] * Table1[Price] )

[dearwatson]

Ah yes indeed, apologies.

What Matt said.

Thanks Matt.

[unclejemima]

Thanks guys for the kind replies.

So I made a new table, then put in the forumla (Where Trandata is the table)

Total Sales = SUMX ( ViewTrandata, ViewTrandata[Quantity] * ViewTrandata[SellPrice] )

I get the error "The expression specified in the query is not a valid table expression."

Am I supposed to add this to the ODBC table or is it ok that I made a new table?

I want this calulation to update based on records as they are updated.  Every day I update my table data and I would like the calculations to update as well.

Let me know and thanks for the help 🙂

[mattbrice]

Table name is "Trandata" or "ViewTrandata"?  Spelling has to be exact of course.  And enclosed in single quotes if has a space in the middle.

And to be clear, i specified a measure, not a calculated column - ok?

[unclejemima]

Ah good to know lol.  I was making a new table (not new measure)

So I created the new measure inside the table "ViewTranData" and it seems happy now, or at least does not throw an error message...but I don't see the output.  I was expecting to see another column created with the calculations displaying of "Quantity x SellPrice"

Am I missing something.

[unclejemima]

Got it sorted.  I see the measure in the table and now I can drag it into the chart.

Only I was hoping to visably see the results from the calculation in the table...is that possible?  Make it easier to verify if they are correct.

[dearwatson]

So... like this?

For the above table you could just create the first two measures in my original post - in this example Unit Price = SUM(Table[Price]) and Qty = SUM(Table[Qty])

Is that what you need?... P.S. there's another issue in this solution which I'm sure someone will point out (hint: Unit Price)

Also in this pic I show why matt's version is correct over mine... in matt's version the SUMX ensures the total sums up properly in the subtotal (bold) line.

[unclejemima]

How do you see that view?  I can't seem to view my measure in a table?

[dearwatson]

This is the new matrix visual (with the yellow border) available as a preview option in the april update.

but you can get a similar view with table or the old matrix (also circled)

[unclejemima]

Fantastic Thanky you!

-mark