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

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.