1

I have one table: STOCK. It has (among other things) a column: EAN and LOCATION.
I'd like to know my total of stock of a certain EAN in LOCATION 'stockroom' or 'return'.

ID - EAN - LOCATION
01 - 3344556677 - stockroom
02 - 3344556677 - stockroom
03 - 3344556677 - sent
04 - 3344556677 - return
05 - 1122889900 - sent
06 - 1122889900 - stockroom
07 - 1122889900 - stockroom (added on 7 dec.2020)

The result should (at least) show me:
EAN - NUMBER IN STOCK
3344556677 - 3
1122889900 - 2 (edited on 7 dec.2020)

What DAX should be used?
Should it be done in a seperate table? Or can it be done in a new column to table 'stock'.

Ekfa
  • 93
  • 3
  • 15
  • assuming the table you posted is `table1` what is your `table2`? if there is join on EAN then you could COUNTROWS with RELATEDTABLE and appropiate filter. Do you use it for other calculations or just wan to show it in UI? – Stachu Dec 04 '20 at 13:46
  • I'm very sorry for this confusion in my question. I tried changing the title, but that doesn't seem to be an option. In reality I have 1 table (simplefied as above). At first I thought of making a second table with all my unique EAN in it. This second table (so I thought) was to be extended per unique EAN with it's totals I have in 'stockroom' and/or in 'return'. – Ekfa Dec 07 '20 at 10:10

1 Answers1

2

If I'm understanding correctly, you want a simple count of rows where the location is stockroom or return.

Number in Stock = 
CALCULATE ( COUNT ( T[ID] ), T[LOCATION] IN { "stockroom", "return" } )

Result

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • this is clever, there is no need to iterate over T[EAN] for correct Totals :) – sergiom Dec 04 '20 at 16:19
  • I’m sorry. That’s not what I mean. I want a number of EAN per unique EAN in the locations “stockroom” and/or “return”. That way I know how many of each EAN I have in stock. – Ekfa Dec 04 '20 at 18:34
  • This gives the 3 and 1 you specified. Can you show an example where it doesn’t give what you expect? – Alexis Olson Dec 04 '20 at 18:39
  • What you are mentioning ("If I'm understanding correctly, you want a simple count of rows where the location is stockroom or return") is not what I mean to figure out. I'd like a total per unique EAN how many I have in stock/return. I don't need a total of EAN in stockroom/return. The numbers perhaps give the 3 and 1, but that's a total of 3 in stockroom and 1 in return. What I'd like is a total of 3 for EAN 3344556677 and a total of 1 for EAN 1122889900. – Ekfa Dec 07 '20 at 10:15
  • I added/edited my original posts, so the totals don't match each other anymore ;-) – Ekfa Dec 07 '20 at 10:19
  • So correction because of the edited example in my initial post: What I'd like is a total of 3 for EAN 3344556677 and a total of 2 for EAN 1122889900. – Ekfa Dec 07 '20 at 10:21
  • The measure still works. I've edited in an image of what it looks like when you use it in a visual. – Alexis Olson Dec 07 '20 at 14:23
  • Thanks! Totally my mistake to begin with. I started out making a new table... This DAX command can just be put in for a new column/measure in the existing table "Stock". It works out fine. – Ekfa Dec 11 '20 at 11:01