Wrong sales amount due to 2 fact tables

[PimFV]

We want to compare 2 fact tables in 1 visual.

One table contains all invoice lines with a link to the “Date” and to the “Customer” table.
The other Fact table concerns a budget per business unit with a link to the “Date” table and the “Customer” table.
In a Gauge visual we want to compare the sales comparing to the budget.

The budget per business unit is displayed perfectly. However, the sales is shown the same for each business unit. This concerns the total sales that has been invoiced.

It therefore seems that because there is no good connection between the 2 fact tables, the sales per business unit is not shown properly. Can someone help me further with this?

Thank you very much!

[v-yetao1-msft]

Hi @PimFV 

Has your problem been solved ? If it has been solved, please accept SU's answer as a solution so that others with similar problems can see it .

Best Regards

Community Support Team _ Ailsa Tao

[edhans]

Did you use the Business Unit field from the customer table in the lower right table? If you used the field from your Budget table, that is the problem. But from the customer table it should filter both the sales and budget fact tables. You should hide the foreign key fields in your fact tables so you never use them in visuals.

[PimFV]

Hello Edhans,

Thanks you for you reaction!

I indeed did use the "Business unit" field from the "Budget" table.

When I use the "Business unit" from the "Customer" Table, I have the problem that not every Business unit already has a customer with sales, so the budget is not shown.

In addition, there is also a part of the budget that is not linked to a customer.  This will be fed by a number of article groups that I set as a filter on the visual and thus exclude it from other visuals.

I hope you can still follow me.

[edhans · Accepted solution]

Ok, this is a model problem. You need to add all customers from your budget table to the customer table if not already there and then it will just work. 

A workaround though is to change the Sales measure. Change it to this:

CALCULATE(
   [YourSalesMeasure],
   CROSSFILTER(
      BudgetTable[CustomerNumber],
      CustomerTable[CustomerNumber],
      BOTH
   )
)

That will turn on bi-directional filtering for that measure only. Note: THIS IS NOT A GOOD PRACTICE. For this instance, the model fix is best practice. There are times to use CROSSFILTER(), which is why it exists, but it isn't for fixing a bad model. All of your measures will get more and more complex and tedious until your customer table is fixed to have all customers listed.