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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Thanks parry2k for the reponse but this doesn't work...

 

Appending the Unique key to the date doesn't create a unique field... due to the duplicated rows (see January in the table provided, the same key and date occurs more than once).

 

Perhaps thats really the essence of the problem, how to strip out the duplicates but without losing the additional columns I need.

parry2k's avatar
parry2k
Super User
8 years ago

Dont' get caught into that I called that column "Unique", this field is getting used in SUMX where is working as a unique value.

 

Did you tried what I suggested? It worked on my end.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    I did try it and I thought it didn't work... but it does... my mistake was looking at the wrong level granularity which changed the context.

     

    My date in reality has Dates in DDMMYYYY (rather than month as I summarised above) and I need to do the deduplication on the level of the month... so keeping what you wrote and looking at the day level gives the right result, but deduplicating against the day but totalling the month gives a different figure.

     

    To get the level of the month I changed the FORMAT for the unique calc column to be '"YYYYMM" and everything works a treat.

     

    Thanks a lot for the help!

    • 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