Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Formula Help

Hello,

 

*Please see attached excel spreadsheet*

 

I was hoping someone could help with a current issue I am having. I am working to create a formula within Power BI that will overall fix the columns to what you see highlighted in yellow. The situation is that users are sometimes inputting incorrect quantities within the system, not inputting them at all, or inputting higher quantities than what they could have been able to work.

Below are some general rules that guide the values that I would like to see.


1) I would like the formula to start over every time a new Order ID is referenced, with the first step in the order being WC ID “HDR”
2) Order Qty is always standard and never changes
3) Compl Op Qty should always count down
4) If there is a difference between a row and the prior rows Compl Op Qty, then that difference should be reported in the current rows At Oper Qty
5) The Sum of At Oper Qty should equal the corresponding Order Qty
6) Amount of sequences is never standard and has infinite range.
7) There is 100’s of different order numbers, and this file would be updated multiple times daily.

 

I have attempted to write a formula into excel, but am still struggling. Ultimately, I would to add to new columns within Power BI query with new formulas that would be able to accomplish the above steps. This formula would repeat every time a new Order ID is referenced.

 

Thank you,

Jason

 

 

 

 

Need Help

5 Replies

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    We can use the following steps to meet your requirement.

     

    1. Create an index column in Power Query Editor.

     

     

    2. Then we can create two calculated column.

     

    Column = 
VAR x =
    CALCULATE (
        SUM ( 'Table'[Compl OP Qty] ),
        FILTER (
            'Table',
            'Table'[Index]
                = EARLIER ( 'Table'[Index] ) - 1
                && 'Table'[OrderID] = EARLIER ( 'Table'[OrderID] )
        )
    )
VAR y =
    IF ( ISBLANK ( x ), 0, x )
RETURN
    IF (
        'Table'[Compl OP Qty] = y,
        'Table'[Compl OP Qty],
        IF (
            'Table'[Compl OP Qty] < y,
            'Table'[Compl OP Qty],
            IF ( y = 0, 'Table'[Compl OP Qty], y )
        )
)

     

    Column 2 = 
VAR x =
    CALCULATE (
        SUM ( 'Table'[Column] ),
        FILTER (
            'Table',
            'Table'[Index]
                = EARLIER ( 'Table'[Index] ) - 1
                && 'Table'[OrderID] = EARLIER ( 'Table'[OrderID] )
       )
    )
var y = IF(ISBLANK(x),'Table'[Column],x)
return
ABS('Table'[Column]-y)

     

    The result like this,

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello,

       

      This looks perfect. Thank you for the assistance. I am still receiving issues. When attempting to add custom column, I receive an error after copying and pasting the provided formula. The error reads "Token Eof expected" .

      • v-zhenbw-msft's avatar
        v-zhenbw-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        Please create two calculate columns in Power BI Desktop not custom column in Power Query Editor. Please refer to following screenshot.

         

         

         

        If you have any questions, please kindly ask here and we will try to resolve it.

         

        Best regards,

         

        Community Support Team _ zhenbw

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.