Forum Discussion

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 nu...

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
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
  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!
  - amitchandak
    Super User
    6 years ago
    upload to one drive or dropbox and share