Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dr_Chris
Helper I
Helper I

Storing sum of items by week in a new table

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  
    
DateProductQtyProd Week
01/11/2020A12020 - 44
01/11/2020A202020 - 44
02/11/2020B32020 - 45
03/11/2020A152020 - 45
03/11/2020C432020 - 45
05/11/2020D22020 - 45
06/11/2020B212020 - 45
06/11/2020A102020 - 45
06/11/2020B22020 - 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
  
WeekTarget
2020 - 4250
2020 - 4350
2020 - 4475
2020 - 4590
2020 - 46110
2020 - 47110

 

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 
   
WeekTargetProduced
2020 - 42500
2020 - 43500
2020 - 447521
2020 - 459025
2020 - 461100
2020 - 471100



Can anyone help me with the correct DAX expression for the [Produced] column please?

Thanks

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@Dr_Chris,

 

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

 

DataInsights_0-1604861146107.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
DataInsights
Super User
Super User

@Dr_Chris,

 

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

 

DataInsights_0-1604861146107.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Amazing - worked first time!

Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.