Forum Discussion
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
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!!
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
6 Replies
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
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.
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.