Max Value of two variables

[rjg2g11]

Hello 

Please see table below - I am trying to calculate the 'Max of both Colours Column'. 

I am trying to calculate the Max In Stock of the two colours from the Shop provided, in a column. For example, Shop A has Black_A and Black_B, and i would like to calculate the max In Stock value of them both, and create a column of that, in this case 1. At the same time, i would also like to find out the MAX in stock value of Silver_A and Silver B. I would like to do this for multiple shops

We havent got anywhere with this, but would appreciate your help!

Thanks

Shop	Colour	In Stock?	Max of both colours
A	Black_A	1	1
A	Black_B	1	1
A	Silver_A	1	1
A	Silver_B	0	1
B	Black_A	0	0
B	Black_B	0	0
B	Silver_A	0	1
B	Silver_B	1	1
C	Black_A	1	1
C	Black_B	1	1
C	Silver_A	0	0
C	Silver_B	0	0

[parry2k]

When you say max of both colors, do you mean color is BLACK or SILVER?

[rjg2g11]

Hi @parry2k

i would like to find the max of Black_A or Black_B and also the max of Silver_A or Silver_B 

thanks 

[parry2k]

understood but what groups black_a and Black_b in your data model.

[rjg2g11]

@parry2k

This is the difficulty i have, nothing groups them - i need to pick them out of the column and find the max in stock value between them.

Thanks again

[vik0810]

Assuming your colours always have schema colour_x and only the part on the left of "_" counts, you can use following calculated column:

Max of colours = 

   MAXX(
    FILTER (
        Tabelle1,
        Tabelle1[Shop] = EARLIER ( Tabelle1[Shop] ) && LEFT(Tabelle1[Colour], FIND("_", Tabelle1[Colour])) = LEFT(EARLIER(Tabelle1[Colour]), FIND("_", EARLIER(Tabelle1[Colour])))
           
    ), Tabelle1[In Stock?])