Summing / retrieving values based on Distinct Key

[Anonymous]

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

UniqueKey4   |    3

Table B contains a list of incidences of Keys by Date, e.g. 

Date               |    _Key

Jan 2018     |    UniqueKey4

Jan 2018     |    UniqueKey3

Jan 2018     |    UniqueKey1

Jan 2018     |    UniqueKey3       *note repeated

Jan 2018     |    UniqueKey1       *note repeated

Feb 2018     |    UniqueKey2      

Feb 2018     |    UniqueKey3

Mar 2018     |    UniqueKey2

I need to find out the QTY in use by month, by distinct Key (ignoring repeated entries for the same date period), e,g,

Date         |          QTY

Jan 2018     |    9

Feb 2018     |    11

Mar 2018     |    10

Help Appreciated!!

[parry2k · Accepted solution]

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]

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]

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]

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]

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

Cheers!!!

P

[linphan]

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