Filtering Data

[mumair]

Here is my example data

Order # | Train # | Product | Location | Tonnage | Load Time

Order 1 | Train A | Product 1 | New York | 72 | 45

Order 2 | Train A | Product 1 | New York | 64 | 53

Order 3 | Train A | Product 1 | New York | 76 | 36

Order 4 | Train B | Product 1 | New York | 45 | 65

Order 5 | Train C | Product 1 | LA | 55 | 67

Order 6 | Train C | Product 2 | LA | 43 | 43

Order 7 | Train D | Product 1 | LA | 35 | 10 

I want to calculate RATE i.e. Tonnage / Load Time. However, I need to calculate it in a specfic way. If the same product is being loaded on the same train, it is assumed to be loaded simulataneously. This can be seen in Orders 1 - 3. Therefore the correct rate is (72 + 64 + 76)/ 53 = 4.0. I also need to know the load rate of product in every location. This would be (4.0 + 45/65 + 55/67 + 35/10)/4 = ~2.25 for Product 1. Furthermore, I need to know the load rate of every location. For LA, this would be calculated as (55/67 + 43/43 + 35/10)/3 = ~1.77. Lastly, I need to konw the rate of a particular product in a particular location. For Product 1 in LA, this would be (55/67 + 35/10) = ~2.16.

How would I write DAX to accomplish these goals?

[v-qiuyu-msft]

Hi @mumair,

You can create measures below. Please see attached .pbix file.

rate = CALCULATE(SUM(Table1[Tonnage]),FILTER(ALLSELECTED(Table1),'Table1'[Train #]="Train A" && 'Table1'[Product]="Product 1"))/
CALCULATE(MAX('Table1'[Load Time]),FILTER(ALL(Table1),'Table1'[Train #]="Train A" && 'Table1'[Product]="Product 1"))

Pro1 = var T=CALCULATE(SUM(Table1[Tonnage]),FILTER(ALLSELECTED(Table1),'Table1'[Train #]=MAX('Table1'[Train #]) && 'Table1'[Product]=MAX('Table1'[Product])))
return
(SUMX(FILTER(ALL(Table1),'Table1'[Product]="Product 1"&& 'Table1'[Train #]<>"Train A"),DIVIDE('Table1'[Tonnage],'Table1'[Load Time]))+'Table1'[rate])/'Table1'[rate]

ForLA = SUMX(FILTER(ALL('Table1'),'Table1'[Location]="LA"),DIVIDE('Table1'[Tonnage],'Table1'[Load Time]))/COUNTAX(FILTER(ALL(Table1),'Table1'[Location]="LA"),'Table1'[Location])

P1-LA = SUMX(FILTER('Table1','Table1'[Location]="LA" && 'Table1'[Product]="Product 1"), DIVIDE('Table1'[Tonnage],'Table1'[Load Time]))

Best Regards,
Qiuyun Yu

[mumair]

Is there a way to make it more general? I don't need it just for those specific calculations, but for all of them across a much larger table.

[mumair]

For clarification, we care about the total time it takes the total product to get on the train in calculating the rate.