Forum Discussion
Need help with the DAX Calculation
Hello, thank you for the support.
Yes, is necessary because with that information I can see the PO/DO will have the material to the order.
It works but some details are missing. Could you help me? I'll tell you what I need.
For example, for product number "SR125", if I change DO to PO, I only detect the first PO, not the second.
The result should be like this:
Other example with the following part number "SR124", the result sould be:
Please see the project pbix in the following link:
Let me know if you need something else.
Thanks!
Hi Erick24 ,
Thank you for your kind reply.
Here are the final results returned for SR125 and SR124, please check to see if they meet your requirements.
This occurs because of a missing unique index key. In the original code, we determine their respective values via DO or PO.
If you change DO to PO, we need to re-add a new unique index to the filter criteria.
Based on the data in the table you provided, you can add filters in several ways.
You could get more detailed information by checking the attachment.
If you have any other concerns, please feel free to share with us here so that we can offer more support.!
Best regards,
Lucy Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Erick24 ,
Thank you very much for your kind reply and I apologise for misinterpreting your needs in my last reply!
Is it necessary to achieve the following effects?
I created a new sum column to calculate the total amount of materials consumed for different types of goods.
Sum of Transaction quantity =
VAR CurrentIndex = 'Table'[Index]
RETURN
IF(
'Table'[Type] = "Order" && 'Table'[Part Number] = "SR125",
CALCULATE(
SUMX(
FILTER(
ALL('Table'),
'Table'[Index] <= CurrentIndex && 'Table'[Type] = "Order" && 'Table'[Part Number] = "SR125"
),
ABS('Table'[Transaction quantity])
)
),
IF('Table'[Type] = "Order" && 'Table'[Part Number] = "SR352",
CALCULATE(
SUMX(
FILTER(
ALL('Table'),
'Table'[Index] <= CurrentIndex && 'Table'[Type] = "Order" && 'Table'[Part Number] = "SR352"
),
ABS('Table'[Transaction quantity])
)
),
BLANK()
))This column is used to differentiate between supplying from PO or DO.
WhatWillBeToSupply =
VAR SR125 = 30
VAR SR352 = 10
VAR POQuantity =
CALCULATE(
MAX('Table'[Transaction quantity]),
FILTER(
'Table',
'Table'[Part Number] = "SR125" && 'Table'[Type] = "PO")
)
VAR DOQuantity =
CALCULATE(
MAX('Table'[Transaction quantity]),
FILTER(
'Table',
'Table'[Part Number] = "SR125" && 'Table'[Type] = "DO")
)
VAR PreviousPO = SR125 + POQuantity
VAR PreviousDO = SR125 + POQuantity +DOQuantity
RETURN
SWITCH(
TRUE(),
'Table'[Sum of Transaction quantity] <= SR125 && 'Table'[Part Number] = "SR125" && 'Table'[Type] = "Order", "In Stock",
'Table'[Sum of Transaction quantity] <= PreviousPO && 'Table'[Part Number] = "SR125" && 'Table'[Type] = "Order", "PO",
'Table'[Sum of Transaction quantity] <= PreviousDO && 'Table'[Part Number] = "SR125" && 'Table'[Type] = "Order", "DO",
'Table'[Sum of Transaction quantity] <= SR352 && 'Table'[Part Number] = "SR352" && 'Table'[Type] = "Order", "In Stock",
BLANK()
)
You could get more detailed information by checking the attachment.
If you have any other concerns, please feel free to share with us here so that we can offer more support.!
Best regards,
Lucy Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello, thank you for the support.
Yes, is necessary because with that information I can see the PO/DO will have the material to the order.
It works but some details are missing. Could you help me? I'll tell you what I need.
For example, for product number "SR125", if I change DO to PO, I only detect the first PO, not the second.
The result should be like this:
Other example with the following part number "SR124", the result sould be:
Please see the project pbix in the following link:
Let me know if you need something else.
Thanks!
Hi Erick24 ,
Thank you for your kind reply.
Here are the final results returned for SR125 and SR124, please check to see if they meet your requirements.
This occurs because of a missing unique index key. In the original code, we determine their respective values via DO or PO.
If you change DO to PO, we need to re-add a new unique index to the filter criteria.
Based on the data in the table you provided, you can add filters in several ways.
You could get more detailed information by checking the attachment.
If you have any other concerns, please feel free to share with us here so that we can offer more support.!
Best regards,
Lucy Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.