Forum Discussion

dyee4613's avatar
dyee4613
Helper I
6 years ago

My Standards Table is Not Filtering Correctly

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. 

Need Help

5 Replies

  • richbenmintz's avatar
    richbenmintz
    Resident Rockstar
    6 years ago

    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's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      amitchandak
      Super User
      6 years ago

      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's avatar
        dyee4613
        Helper I
        6 years ago

        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!