Cannot relate tables

[Oros]

Hello,

I have a main sales table that has dollar sold@cost and dollar sold@retail. 

I have a second table with QTY sold and  a third table with QTY credited.

I would like to add a QTY column to my main sales table but it seems like there is an issue with the table relationships.

Here is the sample pbix file.

https://file.io/GVhIlZM4PyGS 

Any help is highly appreciated.  Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

In the attached PBI file, create a single column table of all unique products from Dollar and Qty tables.  Create a Many to One relationship from Dollar and Qty tables to this new product table.  To your visual, drag Product from the new table.

Hope this helps.

[Anonymous · Accepted solution]

Hi @Oros ,

I updated your sample pbix file(see the attachment), please find the details in it.

1. Delete the relationship between tables

2. Create a calculated column as below

Column = 
VAR _invoiceqty =
    CALCULATE (
        SUM ( 'INVOICE QTY'[QTY INVOICED] ),
        FILTER ( 'INVOICE QTY', 'INVOICE QTY'[PRODUCT] = 'SALES TABLE'[PRODUCT] )
    )
VAR _maxpdate =
    CALCULATE (
        MAX ( 'CREDIT QTY'[POSTING DATE] ),
        FILTER ( 'CREDIT QTY', 'CREDIT QTY'[PRODUCT] = 'SALES TABLE'[PRODUCT] )
    )
VAR _creditqty =
    CALCULATE (
        SUM ( 'CREDIT QTY'[QTY CREDITED] ),
        FILTER ( 'CREDIT QTY', 'CREDIT QTY'[PRODUCT] = 'SALES TABLE'[PRODUCT] )
    )
RETURN
    IF (
        ISBLANK ( _creditqty ),
        BLANK (),
        IF ( 'SALES TABLE'[POSTING DATE] = _maxpdate, _invoiceqty + _creditqty )
    )

Best Regards

[Oros]

Hi @Anonymous ,

This works as well,  Thanks!

[Ashish_Mathur]

Hi,

There is no file there.

[Oros]

Hi @Ashish_Mathur ,

Sorry again.  Here it is:

sample file

[Oros]

Hi @Ashish_Mathur ,

Sorry, here is the file.

sample.pbix

and here is what I would like to achieve.  Thanks!

[Ashish_Mathur]

No file there.

[Oros]

HI @Ashish_Mathur ,

My apology, I change the link.  Thanks.

https://www.sendbig.com/view-files/?Id=cc1d8b88-17eb-6b08-49dd-9526cde7879e

[Ashish_Mathur · Accepted solution]

Hi,

In the attached PBI file, create a single column table of all unique products from Dollar and Qty tables.  Create a Many to One relationship from Dollar and Qty tables to this new product table.  To your visual, drag Product from the new table.

Hope this helps.

[Oros]

Hi @Ashish_Mathur ,

Thank you so much!!! Works like a charm!

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

There is still no file there.  Recheck before you post.

[PijushRoy]

Hi @Oros 

Please check the relationship

https://drive.google.com/file/d/1MCKGaxaw_ZWA_uwrZwNr0PtrcQBYaKo1/view?usp=sharing

If solved your requirement, please mark this answer as SOLUTION.
If this comment helps you, appreciate your KUDOS

Pijush

[Oros]

HI @PijushRoy ,

Thank you for your quick reply.  Maybe I am missing something.

I cannot find in your sample solution the correct QTY column.  I would like to add the QTY column here, where the QTY is the total of QTY Sales and QTY Credits for each product.  If Apple has a total QTY sale of 10 but a return (credit) of 5, then Apple should have a total QTY column of 5.

Thanks again.