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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.