Assign an index by row in a new column based on multiple criteria

[aaron_lee]

Hi,

I am looking for some help with building 2 new columns in my dataset.  They are:

1. Assign an index to each product based on a combination of variables from otherc olumns. This is desired output 1
2. Desired Output2 -  The minimum price for each new group that has been created in desired output 1.

Example:

For desired output 1 i need to find all of the products that are related to each other. See colour coded groups above. I have used the folloing formula but it doesnt give the right result. 

Desired Output 1 = CALCULATE(MINX(testdata,testdata[Group 1]),FILTER(testdata,testdata[Group 2] = EARLIER(testdata[Group 2])))

In SQL we use a CASE statement with PARTION BY to generate the desired output. We would like to use Power BI however as it offers more flexibility.

It may be that a measure rather than column is a better alternative. Open to suggestions and would like some help please

Thanks

Aaron

[JarroVGIT]

Can you explain what the logic is behind DesiredOutput1? Is it - if both values in group1 and group2 haven't occured yet in the table, then up the index by 1?

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[aaron_lee]

yes.

I want to create an index or unique number for groups of products that feature in both group 1 and group 2.

To give some context to the numbers in the table. Group 1 are products that are same type (ie crisps) and size (1 is small small crisps and 2 is large). Group 2 binds the groups together by linking the different sizes to the unique flavours (ie group 3 are small and large salted crisps ). The group then puts together all flavours of small and large crisps. Think pringles...

You can see from the table Group1 products that are either denoted by a 1 or 2 are related in Group 2 in some way. The index/unique number I am trying to create is in effect the super group.

Hope this makes sense and thanks for the response.

[JarroVGIT]

Thanks for trying to clarify the question. I still don't fully understand the logic of grouping the products together based on Group1 and Group2. I understand that the numbers in both columns are reflecting categories from the same list of categories?

Is there a hard mapping between group1 and desiredOutput1? For example: you want to group products from Group1=1 && Group1=2 together, and you want to group products from Group1=emtpy together, and you want to group products from Group1=8 together? If that is hardcoded then you can create a calculated column with Switch:

DesiredOutput1 = SWITCH([Group1], 1, 1, 
                  2, 1, 
                 "", 2, 
                  8, 3)  

Your desiredoutput2 can then be:

DesiredOutout2 = 
VAR currentGroup = Table1[DesiredOutput1]
RETURN
CALCULATE(MIN(Table1[New Price]), FILTER(Table1, Table1[DesiredOutput1] = currentGroup))

Hope this helps you out? Let me know! 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[aaron_lee]

Thanks for the response. Yes the products are in the same product category. 

There are too many groups (1 & 2) to hard code the result (200+ in each). Plus, the products in the groups change weekly so think the solution needs to be dynamic i.e. cycle through all of the combinations in 1 & 2 to build the desired output. 

I've updated the example to help provide some more context

Product Id	Product Detail	Group 1 (Same Size & Different Flavour)	Group 2 (Different Size & Same Flavour)	Combination	Desired Out Put1 (Super Group)
4	Small Crisps - Plain	1		1,	1
7	Small Crisps - BBQ	1	3	1,3	1
17	Large Crisps - BBQ	2	3	2,3	1
12	Small Crisps - Chilli	1	4	1,4	1
13	Large Crisps - Chilli	2	4	2,4	1
11	Small Crisps - Cheese	1	5	1,5	1
14	Large Crisps - Cheese	2	5	2,5	1
9	Small Crisps - Salted	1	6	1,6	1
15	Large Crisps - Salted	2	6	2,6	1
16	Large Crisps - Tangy	2		2,	1
18	Yoghurt	-	-	-,-	2
24	Milk Full Fat	8	-	8,-	3
20	Mulik Semi Skimmed	8	-	8,-	3
21	Carrot	-	-	-,-	4
200	Burger	-	-	-,-	98
250	Cheese	-	-	-,-	100

For the crisps group. I have re-sorted the example to better show how the products relate to each other in terms of product size and flavour. Where there isnt different sizes of a flavour i.e. Small plain and Large tangy, these are still part of the desired output as they are part of Grouping 1 where other flavours are bound to group 2 through different sizes 

I hope this makes sense

[aaron_lee]

I forgot to say i should have made it clear that my example was just an exmple rather than the total dataset. Apologies!