Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Summing / retrieving values based on Distinct Key

I have two related tables   Table A contains a unique key and some qty attached to it, e.g.    _Key                 |    QTY UniqueKey1    |    5 UniqueKey2   |    10 UniqueKey3    |    1 Uni...
Need Help
  • parry2k's avatar
    parry2k
    8 years ago

    Here is what you need to, set relationship between both tables on key field.

     

    and add following column in Tableb

     

    Qty = RELATED(Tablea[Qty])

    Unique =  FORMAT(Tableb[Date], "YYYYMMDD") & Tableb[Key]

     

    Add following measures in table b

     

    Max Qty = MAX(Tableb[Qty])

    Qty SUM = SUMX(VALUES(Tableb[Unique]),[Max Qty]) 



    Add table visual, drop date from tableb and Qty SUM measure

parry2k's avatar
parry2k
Super User
8 years ago

No worries, I'm glad it worked out for you:)

 

Cheers!!!

P

linphan's avatar
linphan
Frequent Visitor
8 years ago

Hi All,

 

I got the same issue. I have 1 table with a lot of repeated values, only qty is unique as below screen

 

What I want to display is 1 line with sum of qty

 

I tried to create new table Billed Qty = SUMMARIZE(Billing,Billing[Document Number],"Billing Qty",sum(Billing[Billed Quantity])) but I  just have 1 column from orginal table. How can I get the remaining columns?

Plase help. Thanks very much