How can I show the same amount by the same Branches invoice and payment?

[alikemalaydin]

Hi,

I have two different datasets.

Invoices and payments.

Invoice data has Branch,Company_Code,Company_Name,Invoice_Amount

Payment data has Branch,Company_Code,Company_Name,Payment_Amount

These two data connected to each other Company_Code.

If I made a table and use Slicer to filter the data by Branch, Amount summarizes filtered the only which I used table branch. 

Example:
I have 5 records under different Branches. Also Payment.
How can I show the same amount by the same Branches invoice and payment?

Report Table:

Customers	Invoice amount	Payment Amount
Customer Name	11.100	1.600

Data: Invoice

Company Code	Branch	Customers	Invoice Amount
12345	A	Customer A	1.600
12345	B	Customer A	2.000
12345	C	Customer A	2.000
12345	D	Customer A	2.500
12345	E	Customer A	3.000

Data:Payment

Company Code	Branch	Customers	Payment Amount
12345	A	Customer A	1.600
12345	B	Customer A	2.000
12345	C	Customer A	2.000
12345	D	Customer A	2.500
12345	E	Customer A	3.000

11.100 comes from 5 records. 1.600 comes from 1 record because Slicer selected Payment's table Branch. 

I want to filter also invoice amount by the Payment's Branches. 1.600 - 1.600

Customer Total Invoice Total Payment
Customer Name 18.384 1.600

Thanks to advise.

[alikemalaydin]

Hi,

I've just realized.

If there is no payment or one of the invoices, it does not appear in the table as zero.

How can I fix it?

Customer	Total Invoice	Total Payment
Customer A	5000	0
Customer B	0	2000

[PaulDBrown]

@alikemalaydin 

You can solve this by simply adding "+ 0" to your measures. For example:

Sum of Payments = SUM(Table [payment amount]) + 0

[alikemalaydin]

Thank you so much +0 works well.

[PaulDBrown · Accepted solution]

@alikemalaydin 

By your description, (assuming you have more than one customer by branch), all you need to do is:

1) delete the company_code relationship between both fact tables

2) create a new dimension table for "Branch" for the unique "Branch" values and join this to each fact table with a one-to-many  relationship with the corresponding Branch fields in each fact table

3) create a dimension table with the unique values for "Customer", and join this table to both your fact tables in a one-to many  relationship with the corresponding customer field in each fact table.

4) the create simple sum measures for invoice amount and payment amount.

5) create a table visual with the Customer field from the newly created dimension table, and both your [invoice amount] and [payment amount] measures.
6) use the Branch field from the newly created Branch dimension table as your slicer.

[alikemalaydin]

Hi,

I think it works well now.

Thank you so much.

Can I use same way for dates?

Example date dimension table.

It has been a few months since I started using Power Bi.
It's fun but formulas and logic are not like excel. 🙂
my table relationship like below.
Maybe it is a little bit confuse or a long way. 🙂

[PaulDBrown]

@alikemalaydin 

Sure you can use the same idea for dates. It's actually a "best practice" to set up a date table with continuous dates covering the range of dates in your model as a dimension table.

let us know if you need help!