Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I've searched the forum and tried to get this to work but still stumped.
I've got a table from production that records all the items booked into the warehouse. There can be multiple entries for each product each day as they are simply scanned as they come into the warehouse. The Production-Actual table contains many different field including the following ([Prod Week] is one that I have already created based on the date)
Production-Actual | |||
Date | Product | Qty | Prod Week |
01/11/2020 | A | 1 | 2020 - 44 |
01/11/2020 | A | 20 | 2020 - 44 |
02/11/2020 | B | 3 | 2020 - 45 |
03/11/2020 | A | 15 | 2020 - 45 |
03/11/2020 | C | 43 | 2020 - 45 |
05/11/2020 | D | 2 | 2020 - 45 |
06/11/2020 | B | 21 | 2020 - 45 |
06/11/2020 | A | 10 | 2020 - 45 |
06/11/2020 | B | 2 | 2020 - 45 |
For each prodcuct we have a second table which holds (along with other information) the weekly production targets for that product - for instance, for Item A
Target-Product A | |
Week | Target |
2020 - 42 | 50 |
2020 - 43 | 50 |
2020 - 44 | 75 |
2020 - 45 | 90 |
2020 - 46 | 110 |
2020 - 47 | 110 |
I'd like to add a new column to "Target-Product A" which holds the total number of that product produced each week, so for Product A we would have
Target-Product A | ||
Week | Target | Produced |
2020 - 42 | 50 | 0 |
2020 - 43 | 50 | 0 |
2020 - 44 | 75 | 21 |
2020 - 45 | 90 | 25 |
2020 - 46 | 110 | 0 |
2020 - 47 | 110 | 0 |
Can anyone help me with the correct DAX expression for the [Produced] column please?
Thanks
Solved! Go to Solution.
Try this calculated column in table Target-ProductA:
Produced =
VAR vProduct = "A"
VAR vWeek = 'Target-ProductA'[Week]
VAR vProductionTable =
FILTER (
'Production-Actual',
'Production-Actual'[Product] = vProduct
&& 'Production-Actual'[Prod Week] = vWeek
)
VAR vResult =
SUMX ( vProductionTable, 'Production-Actual'[Qty] )
RETURN
vResult
Proud to be a Super User!
Try this calculated column in table Target-ProductA:
Produced =
VAR vProduct = "A"
VAR vWeek = 'Target-ProductA'[Week]
VAR vProductionTable =
FILTER (
'Production-Actual',
'Production-Actual'[Product] = vProduct
&& 'Production-Actual'[Prod Week] = vWeek
)
VAR vResult =
SUMX ( vProductionTable, 'Production-Actual'[Qty] )
RETURN
vResult
Proud to be a Super User!
Amazing - worked first time!
Thanks
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |