Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GUSers
Regular Visitor

Need Help with Direct Query Formulas for finding Extended Amounts

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.

1 ACCEPTED SOLUTION
ppm1
Solution Sage
Solution Sage

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

Microsoft Employee

View solution in original post

2 REPLIES 2
GUSers
Regular Visitor

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
Solution Sage
Solution Sage

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

Microsoft Employee

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.