Product Grouping Index Problem

[George1973]

Hi All,

I have a following sample data table:

Here is it in Table form:

Prod_Code	Fist_Sales_Date	Analog_Group	Importance	Group_Prod_Code
Code_1	01-02-18	1		Code_2
Code_2	15-03-17	1	Spectrum	Code_2
Code_3	02-08-17	2		Code_7
Code_4	01-02-22	3		Code_4
Code_5	10-10-21	1		Code_2
Code_6	12-11-20			Code_6
Code_7	30-05-16	2	Spectrum	Code_7
Code_8	20-05-18			Code_8
Code_9	20-11-22			Code_9
Code_10	20-05-18	3		Code_4

The goal is the get grouped the codes in the last column "Group_Prod_Code" based on following criteria:
1. Column "Analog_Group" represents groups among the set of product codes. Some of products may have "Analog Groups", but some may not
2. Within the Analog groups, there are some "Leading" products, called "Spectrum" (Column "Importance)
3. So, here are the final desired results to get:

3.1. If "Analog Group" has "Spectrum", then The "Group_Prod_Code" should be the one, which is generated from the "Spectrum product" within the group - This is the first criteria ((Example: Group#1 and 2)
3.2. If there is no "Spectrum", then the "Leading" product and respectively the "Group_Prod_Code" should be the product, with the latest "Sales Date". Column "First Sales Date" (Example: Group#3)
3.3. If there is no Analog_Group, then the End result for "Group_Product_Code" should be the same as the original product code

I Have managed to generate a "Calculated Column" grouping the analog_groups with only "Latest Sales Date", however I can not add the first criteria with "Spectrum" 😞

Analog_Leader_Code = 
VAR Vol=C_GOODS[ANALOG_GROUP]
VAR Miz=C_GOODS[MIZNIAN]
VAR MX=CALCULATE(max(C_GOODS[First Prod Sales Date]),FILTER(C_GOODS,C_GOODS[ANALOG_GROUP]=Vol))
Return
If(Vol=BLANK(),C_GOODS[Prod_Code],
    //if(Miz="Spectrum", IFERROR(LOOKUPVALUE(C_GOODS[პროდუქციის კოდი],C_GOODS[ANALOG_PROD_GROUP],Vol,C_GOODS[MIZNIAN],"Spectrum"),BLANK()),
    IFERROR(LOOKUPVALUE(C_GOODS[Prod_Code],C_GOODS[ANALOG_GROUP],Vol,C_GOODS[First Prod Sales Date],MX),BLANK())
    )

Thanks in advance,

[wdx223_Daniel]

Analog_Leader_Code =
VAR Vol=C_GOODS[ANALOG_GROUP]
RETURN

IF(Vol="",C_GOODS[Prod_Code],COALESCE(MAXX(FILTER(C_GOODS,C_GOODS[ANALOG_GROUP]=Vol&&C_GOODS[MIZNIAN]="Spectrum"),C_GOODS[Prod_Code]),MAXX(TOPN(1,FILTER(C_GOODS,C_GOODS[ANALOG_GROUP]=Vol),C_GOODS[First Prod Sales Date]),C_GOODS[Prod_Code])))

[FreemanZ · Accepted solution]

hi @George1973 

try to add a column like:

Group_Prod_Code2 = 
VAR _grouptable =
FILTER(
    GOODS,
    GOODS[Analog_Group] = EARLIER(GOODS[Analog_Group])
)
VAR _spectable =
FILTER( _grouptable, GOODS[Importance] = "Spectrum" )

VAR _condition1 =
COUNTROWS(_spectable)=1

VAR _speccode =
MINX( _spectable, GOODS[Prod_Code])

VAR _lastsalesdate =
MAXX(_grouptable, GOODS[Fist_Sales_Date])

VAR _lastsalescode =
MAXX( FILTER(_grouptable, GOODS[Fist_Sales_Date] =_lastsalesdate), GOODS[Prod_Code] )

RETURN
SWITCH(
    TRUE(),
    _condition1, _speccode,
    [Analog_Group]<>BLANK(), _lastsalescode,
    [Prod_Code]
)

it worked like:

[George1973]

Thanks a lot! Yes, it works now.

Great! 🙂