Forum Discussion

Oros's avatar
Oros
Post Prodigy
2 years ago
Solved

Cannot relate tables

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!

 

 

 

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    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's avatar
    Anonymous
    2 years ago

    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

13 Replies

    • Oros's avatar
      Oros
      Post Prodigy
      2 years ago

      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.

       

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Oros
      Post Prodigy
      2 years ago

      Hi Anonymous ,

       

      This works as well,  Thanks!