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

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