SUMX between two tables

[Anonymous]

Hi All,

I have two fact tables with different granularities. One with sales forecast per market per city, and one with the share of sales forecast per market per product grouping. The idea behind this setup would be to calculate a theoretical sales forecast per market per city per product group by multiplying the sales forecast per market per city by the share of sales forecast per product group in its respective market.

So for example, sales forecast in London, UK is EUR 1000, share of sales forecast for Deodorants in UK is 50%, making the sales forecast for Deodorants in London, UK 1000 * 50% = EUR 500.

In the attached file I created a SUMX formula that seems to be working, but I cannot figure out why for the life of me, would appreciate any advice!

I have a standalone dim_markets table, which is the common dimension in the two fact tables, and this dim_markets table has a 1-to-many relationship towards the fact tables with a single cross-filter direction. 

Using the 'market' column from this dim_markets table in the visuals, I can see why this would work, because it filters both tables.

However, when I use 'market and city level's own 'market' column, I still get the correct result and have no idea why. Please see below. 

The 'Share of sales forecast' is obviously off, however, the SUMX formula still works, so it seems to be using the correct 'Share of sales forecast' even though in the visual it is wrong. Could it be that somehow, because of how SUMX works, the 'market' filter forces itself onto the standalone dim_market table somehow regardless of the single cross-filter direction in the data model?

The formula for my SUMX sales forecast is:  SUMX('market and city level data', [Sales Forecast] * [Share of sales forecast])

pbix file 

Thanks a lot,

Csaba

[dax]

Hi @Anonymous ,

You could refer to my sample for details. If this si not waht you want, please correct me and inform me more details(such as your expected output).

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Zoe!

Thanks a lot for looking into this! Indeed with your solution the share of sales forecast looks fine, too.

I know my post was quite long but my question actually was: why does my seemingly wrong solution show the correct '

SUMX test - Sales forecast'? 

Again, the formula for my SUMX sales forecast is:  SUMX('market and city level data', [Sales Forecast] * [Share of sales forecast]). 

So for example in the top row in the table on the left (my solution), multiplying EUR 15 by 0,95 should definitely not result in EUR 4,50, but it still does somehow.

Just to double clarify, I get the correct results in my solution, and I am just trying to understand why.

Thanks a lot again,

Csaba

[Anonymous]

Sorry, somehow the picture of the data model got lost, here it is: