Forum Discussion

PBOBOP's avatar
PBOBOP
Icon for Helper I rankHelper I
1 year ago
Solved

Identifying When Item Categories On an Order Change

Hello, Below is a list of item changes on different customer orders. Each item is either in the red category or blue category. There are 5 types of order changes I would like to identify, can someo...
Need Help
Tips and Tricks
  • Irwan's avatar
    Irwan
    1 year ago

    hello PBOBOP 

     

    here is what i get form your description:

    1. in same order number, if there is no value in Quantity Added then it will be "Removing Items"

    2. in same order number, if there is no value in Quantity Substracted then it will be "Adding Items"

    3. in same order number AND same category AND either sum of Quantity Added greater than Quantity Substracted or sum of Quantity Added less than Quantity Substracted, it will be "Changing Items"

    4. Red to Blue or Blue to Red as your explanation, except if both Red and Blue are negative then use ABS value.

     

     

    create a calculated column with following DAX

    Change Type =
    var _Red = 
    SUMX(
        FILTER(
            'Table',
            'Table'[Order Number]=EARLIER('Table'[Order Number])&&
            'Table'[Category]="Red"
        ),
        'Table'[Quantity Added]+'Table'[Quantity Subtracted]
    )
    var _Blue = 
    SUMX(
        FILTER(
            'Table',
            'Table'[Order Number]=EARLIER('Table'[Order Number])&&
            'Table'[Category]="Blue"
        ),
        'Table'[Quantity Added]+'Table'[Quantity Subtracted]
    )
    var _Added = 
    SUMX(
        FILTER(
            'Table',
            'Table'[Order Number]=EARLIER('Table'[Order Number])
        ),
        'Table'[Quantity Added]
    )
    var _Substract = 
    SUMX(
        FILTER(
            'Table',
            'Table'[Order Number]=EARLIER('Table'[Order Number])
        ),
        'Table'[Quantity Subtracted]
    )
    var _CountCategory = 
    CALCULATE(
        DISTINCTCOUNT('Table'[Category]),
        FILTER(
            'Table',
            'Table'[Order Number]=EARLIER('Table'[Order Number])
        )
    )
    Return
    IF(
        ISBLANK(_Added),
        "Removing Item",
    IF(
        ISBLANK(_Substract),
        "Adding Items",
    IF(
        (_Added>=_Substract||_Added<=_Substract)&&_CountCategory=1,
        "Changing Items",
    IF(
        (_Blue<0&&_Red<0)&&_Blue>_Red,
        "Blue to Red",
    IF(
        (_Blue<0&&_Red<0)&&_Blue<_Red,
        "Red to Blue",
    IF(
        _Blue>_Red,
        "Red to Blue",
    IF(
        _Blue<_Red,
        "Blue to Red"
    )))))))
    kind of messy but hope you can get the idea where to start.
    You can tweak the DAX to be more efficient.
     
    Thank you.
Irwan's avatar
Irwan
Icon for Super User rankSuper User
1 year ago

hello PBOBOP 

 

here is what i get form your description:

1. in same order number, if there is no value in Quantity Added then it will be "Removing Items"

2. in same order number, if there is no value in Quantity Substracted then it will be "Adding Items"

3. in same order number AND same category AND either sum of Quantity Added greater than Quantity Substracted or sum of Quantity Added less than Quantity Substracted, it will be "Changing Items"

4. Red to Blue or Blue to Red as your explanation, except if both Red and Blue are negative then use ABS value.

 

 

create a calculated column with following DAX

Change Type =
var _Red = 
SUMX(
    FILTER(
        'Table',
        'Table'[Order Number]=EARLIER('Table'[Order Number])&&
        'Table'[Category]="Red"
    ),
    'Table'[Quantity Added]+'Table'[Quantity Subtracted]
)
var _Blue = 
SUMX(
    FILTER(
        'Table',
        'Table'[Order Number]=EARLIER('Table'[Order Number])&&
        'Table'[Category]="Blue"
    ),
    'Table'[Quantity Added]+'Table'[Quantity Subtracted]
)
var _Added = 
SUMX(
    FILTER(
        'Table',
        'Table'[Order Number]=EARLIER('Table'[Order Number])
    ),
    'Table'[Quantity Added]
)
var _Substract = 
SUMX(
    FILTER(
        'Table',
        'Table'[Order Number]=EARLIER('Table'[Order Number])
    ),
    'Table'[Quantity Subtracted]
)
var _CountCategory = 
CALCULATE(
    DISTINCTCOUNT('Table'[Category]),
    FILTER(
        'Table',
        'Table'[Order Number]=EARLIER('Table'[Order Number])
    )
)
Return
IF(
    ISBLANK(_Added),
    "Removing Item",
IF(
    ISBLANK(_Substract),
    "Adding Items",
IF(
    (_Added>=_Substract||_Added<=_Substract)&&_CountCategory=1,
    "Changing Items",
IF(
    (_Blue<0&&_Red<0)&&_Blue>_Red,
    "Blue to Red",
IF(
    (_Blue<0&&_Red<0)&&_Blue<_Red,
    "Red to Blue",
IF(
    _Blue>_Red,
    "Red to Blue",
IF(
    _Blue<_Red,
    "Blue to Red"
)))))))
kind of messy but hope you can get the idea where to start.
You can tweak the DAX to be more efficient.
 
Thank you.
PBOBOP's avatar
PBOBOP
Icon for Helper I rankHelper I
1 year ago

That works well, thank you so much! 😊