cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PimFV
Regular Visitor

Wrong sales amount due to 2 fact tables

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!

 

PowerBI Numbers.pngRelations.png

1 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

4 REPLIES 4
Ailsa-msft
Community Support
Community Support

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
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
PimFV
Regular Visitor

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.

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.