Related() function between tables with different numbers of rows

[Luca2020]

Hello, 

 I have a problem in creating a formula which gives me the amount of a document divided by different months: 

In table 1, when our salespeople make a quote (N. Doc), they enter the amount (Amount) for each product sold (Product) and the expected billing date (Invoice Date). For instance, N. Doc 1 consists in two different products we expect to sell in july for 300 euro in total (170 + 130 euro). 

In table 3 I use the Summarize function to summarize Table 1 by removing the division by products and keeping only the total for document.

Subsequently, it is decided that the order will be invoiced in different months, therefore table 2 shows the actual dates of the invoices for each document.  For instance, the expected billing date of Doc 1 was july, however, only 40% of the amount was sold in july, while the rest in august. 

I have to create a formula that allows me to divide the amount in table 3 using the % of table 2:

Doc. 1

300*0,4 = 120 in july

300*0,6 = 180 in august

Doc. 2

400*1 = 400 in may

All the table are connected by the number of document.

Thanks in advance

[Pragati11]

Hi @Luca2020 ,

You can try something like:

CalcTest = DIVIDE(YourTable1[Column1], RELATED(YourTable2[Column2]))

Thanks,

Pragati

[Luca2020]

Hi, 

the DIVIDE() function didn't work, however I found out that the reason why I couldn't use the RELATED() function I originally intended to use, was because in Table 1 there were also documents from another of our society so I had to use to many-to-many relation since some documents shared the same number. 

I created a new Key merging the N. Doc and Society name columns and I was able to use the one-to-many relation, then I create a new Amount column in table 2 with the amount taken from the summarize table 3 using the Related() function. 

Thanks, 

[dax · Accepted solution]

Hi @Luca2020 , 

You also could refer tomy sample for details.

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.