Need Help with Direct Query Formulas for finding Extended Amounts

[GUSers]

I am having issues with calculating some data. I have two tables of data.

• Table 1 has a unit's selling price.
• Table 2 has the quantity of units sold based on a date

I've got a Measure with the following formula added into Table 2:

xExtAmt = SUM('Table 2'[Units]) * SUM('Table 1'[SellingPrice])

For the most part it works great, but I've been trying to use some Matrix (Pivot), Card, and Table visuals to calculate some totals and the calculations are WAY off. I'm guessing there is something wrong with what I'm doing here, but I'm not sure what. Between the tables, there is a unique ID field that connects the Unit w/Price and the unique date record of the sale.

I think I may be struggling with what is allowed or not allowed with Direct Query datasets. Any ideas/guidance would be much appreciated. Currently, I'm stuck with a dataset that should be telling me $7.8M, but instead I'm getting $46M. Way off.

[GUSers]

Thanks @ppm1. I need the Direct Query to have live data. I don't want to (nor do I have the capacity to) manually perform refreshes and republish things.

The SUMX function does seem to be doing the trick. I've implemented it in one of the two files that I'm working with and it appears to have done the trick in the first one. Haven't had time yet this week to dive in fully on my Power BI projects, but hoping to close the door on this today/tomorrow and continue my progress. Thanks for the support, figured it was something pretty minor. Simple function change. 👍

[ppm1 · Accepted solution]

Do you really need Direct Query? In any case, what is the relationship between your tables? You likely need an expression like this (assuming you have 1:M (Table2:Table1).

= SUMX( Table2, Table2[Units] * RELATED(Table1[SellingPrice]) )

Pat