Using SUMX across two tables that aren't directly related.

[JWLEsper]

Hi there.

I started writing this because I had an issue I couldn't solve, but now I have solved it and wanted to get some insights on whether there is a better/smarter way to do it.

My goal is to estimate the value of our futre stock by combining our stock forecast with the average price we have paid for our current stock.

I have two fact tables that are linked through one relevant dimension:

FT_StockFigures - containing all current stock. Each line represent an item number at a location. Relevant attributes are [Costprice] and [Ton]

FT_StockAndOrders - is the forecast on what we expect to have in stock for (almost) any future date, relevant attribute is [Ton]

Dim_Items - the two fact tables are linked through here by ItemID, dimension also have attributes for Item Number and Item Group Name which is used to present the data.

My aim is to use [Costprice] and [Ton] from FT_StockFigures til calculate an average stockprice/kg and then multiply it by the expected [Ton] from FT_StockAndOrders to get an estimate of the future value of our stock.

Intial way of doing it is 

Price/Ton = Divide(sum(FT_StockFigures[Costprice]),sum(FT_StockFigures[Ton]))

Future Stock = Sum(FT_StockAndOrder[Ton])

Future Value = [Price/Ton] * [Future Stock]

Which gives the correct result for each item number, but an incorrect result when doing totals and subtotals:

With the Future Stock values of €924 and €-142 being correct - but not adding up to the total, total should be €782 (and divided by the Future Stock the total Price/Ton should be €29,16).

I tried using various combinations of SUMX and AVERAGEX to calculate Price/Ton and Future Stock Value but neither seems to do the job, sometimes I get a better result and sometimes I don't, but I can't make it perfect.

In the end I managed to get the correct result by creating a calculated column in dim_Items and the following SUMX

SUMX(Dim_Items,[Calculated Column] * [Future Stock])

Which provides the correct result with a total of €781.

However I think it seems a bit backward, that I can't do the calculation without a calculated column just because the two tables are only indirectly related. 

Could you think of a smarter way to do this?

Is it true that SUMX only works on directly related tables (and/or is it possible to use it on directly related tables?

Any help/feedback is much appreciated.

[AnilKapkoti]

I’ve observed this behavior in my project as well.

A better way to handle this type of calculation is:
Instead of using SUMX with the dimension table, use the fact table as the table reference. This will give you the correct result.
SUMX ( FT_StockAndOrders, FT_StockAndOrders[Ton] )

[Shahid12523 · Accepted solution]

Your original [Price/Ton] * [Future Stock] works per item but breaks at totals due to unweighted averages.
o fix totals without a calculated column,

use:
FutureStockValue =
SUMX(
VALUES(Dim_Items[ItemID]),
[AvgPricePerTon] * [FutureStockPerItem]
)

- Or use TREATAS to simulate relationships inside SUMX.

Key insight: SUMX works fine across indirectly related tables if you bridge them with shared dimensions or virtual relationships.

[JWLEsper]

This was exactly the type of reply I hoped to get out of my post, thank you!

[FBergamaschi]

Hi @JWLEsper,

first off you did a great job solving. Second, yea you can do it in a full dinamic way with a measure, but if you are good off with what you have, there is nothing wrong with what you did. Point is that SUMX need to do the calculation item by item if you want a full dinmaic solution.

If you want it, please can you share the pbix via some cloud service so I can create the full dinamic version for you? Or  Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI