Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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
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.
For clarification, we care about the total time it takes the total product to get on the train in calculating the rate.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |