My Standards Table is Not Filtering Correctly

[dyee4613]

Goal: This report calculates the number of days worked per cost center (program).  It multiples the number of days work by a standard depending on the item (apron, arm sleeve, etc).  It takes that number and compares it to the actual consumption.  

Problem: My data model, as it currently stands, is not filtering on cost center.  As a result, it shows multiple entries skewing my ability to perform the standard x days worked calculation.  Here is an example. 

Here is my formula.

Expected Qty =

Expected Qty =

If(MAX(Sheet2[Items]) = "Apron",SUM('Crew Ledger'[Emp Count])*MAX(Standards[Apron]),

If(MAX(Sheet2[Items]) = "Arm Sleeve",SUM('Crew Ledger'[Emp Count])*MAX(Standards[Arm Sleeve]),

If(MAX(Sheet2[Items]) = "Beardnets",SUM(MaleEmp[Emp Count])*MAX(Standards[Beardnets]),

If(MAX(Sheet2[Items]) = "Cone Cups",SUM('Crew Ledger'[Emp Count])*MAX(Standards[Conecups]),

If(MAX(Sheet2[Items]) = "Gloves - Diesel",SUM(Diesel[EmpCount])*MAX(Standards[Diesel]),

If(MAX(Sheet2[Items]) = "Hairnets",SUM('Crew Ledger'[Emp Count])*MAX(Standards[Hairnets]),

If(MAX(Sheet2[Items]) = "Gloves - Nitrile",SUM(Nitrile[Emp Count])*MAX(Standards[Nitrile]),

If(MAX(Sheet2[Items]) = "Trash Bags",SUM('Crew Ledger'[Emp Count])*MAX(Standards[Trash Bags])))))))))

I know this isn't a lot to go on.  I'm pretty sure my issue has to do with my inability to connect my standards table with my item table. 

[richbenmintz]

@dyee4613 ,

I would suggest that you re build your standards table, so that it contains 2 columns: Item and standard_value. you could then join the standards to sheet2 and get the appropriate standard for each item. 

As @amitchandak suggested a copy of the pbix file and expected output would go a long way in helping the community solve your issue

[amitchandak]

I doubt calculation has been approached in the right manner.

The one thing you can try quickly is

Expected Qty 1 = calculate([Expected Qty],values(MAX(Sheet2[Items]))

Can you share sample data and sample output. And explain to us on excel; how these calculations need to be done.

[amitchandak]

Few ways , you can move data across tables

Sample

New column in Table 1 = maxx(filter(table2,table1[customer] = table2[customer] && table2[option]="construction",table2[value])

New column in Table 1 = maxx(filter(table2,table1[Attribute] = table2[name] && table1[project] = table2[project]),table2[name])

[dyee4613]

@amitchandak 

@richbenmintz 

Thanks for your feedback!  I have created a sample file.  The problem is that the program field does not filter to the item level.  I need to modify sheet2 in some way to include program information so it filters.  

Note: The item table doesn't make a lot of sense in this sample because it's a lot of work to recreate and isn't that relevant to this problem.  However, I do need the sheet2's item to tie to the item table so I can add in quantity/amt.  

Here is a sample of the data with ProgA selected and ProgB selected.  Notice the standard is the same for both.  It's because I'm using the MAX function and it can't identify progA,progB,progC so it takes the max of all 3.

I don't know how to send the PBIX file through here.  Please let me know where/how to upload it.  Thank you!

[amitchandak]

upload to one drive or dropbox and share