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.
Hi,
For each 'MFG_ORDER_NAME' and 'OP_SEQ_NUMBER' and I need to calculatie the sum of 'RESOURCE_REQUIRED_AMOUNT' and sum of transaction quantity. The last one is easy and I do not need help with that.
The problem lies within the resource required amount. If 'RESOURCE_SEQ_NUMBER'= 20 AND 'BASIS' is "Lot", the sum within this example should be 160. For each transaction quantity row, the underlying table shows 160. If 'RESOURCE_SEQ_NUMBER' = 30 AND 'BASIS' is "Item", the sum should be the 810. This is the line qty of 270 * Order qty based in the table JOBS (linked but not shown here).
Thanks for any help!
Solved! Go to Solution.
Assume your data table(according to your screenshot) is like
If the table JOBS is like this, and there a relationship built between two tables (it is better to set cross filter direction "Both")
ORDER_NAME | qty |
1 | 3 |
2 | 4 |
3 | 5 |
4 | 6 |
5 | 7 |
Then create a measure
Measure = IF(MAX([col4])=20&&MAX([col5])="Lot",MAX([col6]),IF(MAX([col4])=30&&MAX([col5])="Item",MAX([col6])*MAX(Sheet12[qty])))
Best Regards
Maggie
Assume your data table(according to your screenshot) is like
If the table JOBS is like this, and there a relationship built between two tables (it is better to set cross filter direction "Both")
ORDER_NAME | qty |
1 | 3 |
2 | 4 |
3 | 5 |
4 | 6 |
5 | 7 |
Then create a measure
Measure = IF(MAX([col4])=20&&MAX([col5])="Lot",MAX([col6]),IF(MAX([col4])=30&&MAX([col5])="Item",MAX([col6])*MAX(Sheet12[qty])))
Best Regards
Maggie
Thanks Maggie. Problem solved!
Now the individual lines are correct but the sum of 'RESOURCE_REQUIRED_AMOUNT' is incorrect, which now is 13.5
This should be 32.85. I used following calculation.
Hrs_routing = IF(MAX(XXBI_MFG_RESOURCE_TXNS_V[RESOURCE_SEQUENCE_NUMBER])=20 && MAX(XXBI_MFG_RESOURCE_TXNS_V[BASIS])="Lot",max(XXBI_MFG_RESOURCE_TXNS_V[RESOURCE_REQUIRED_AMOUNT])/60,(MAX(XXBI_WIP_JOBS_V[QUANTITY_COMPLETED])*MAX(XXBI_MFG_RESOURCE_TXNS_V[RESOURCE_REQUIRED_AMOUNT]))/60)
most likely you need to use SUMX with RELATED
https://docs.microsoft.com/en-us/dax/sumx-function-dax
https://docs.microsoft.com/en-us/dax/related-function-dax
Something like
Measure =SUMX ( Table, [RESOURCE_REQUIRED_AMOUNT] * RELATED ( JOBS[Quantity] ) )
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 |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |