Forum Discussion
Helper IIdentifying When Item Categories On an Order Change
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.
Super Userhello PBOBOP
what is overall by overall there is more blue than red and overall there is more red than blue?
is this countrow of blue compared to red? or sum difference between blue and red?
Thank you.
Helper IHi Irwan,
It is somewhat countrow.😊
For example in the order 3070636629, 3 out of the 4 rows where quantities are subtracted are red items, which means most of the original order was red. and then all of the new added quantities are all from the blue category, so the order is changing from red to blue.
For order 3070608193, all of the quantitites being subtracted are from the blue category, and all the quantities added are from red category, so the order is changing from blue to red.
So it is more of the category of the majority of the items being subtracted is the original category. and the category of the majority of items being added is the category that it has changed to.
Thank you
- Irwan
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
That works well, thank you so much! 😊