Grouping data

[sabseb2225]

Hello,

I am trying to find solution how to group prices automatically only when concern the same article number.

For example: I sent 6 quotes to different suppliers in order to qoute article 100234.

I have received 5 quotes back. I am going to place the order for article with price $19.

I would like to categorize where rest of suppliers are in terms of prices:

Article no.	Supplier	Price	quote date	won	quoted
100234	abc	20	1/22/2022		yes
100234	erdw	19	1/24/2022	x	yes
100234	ccc	30	1/27/2022		yes
100234	bgfs	48	1/27/2022		yes
100234	gtgthysr\				no
100234	qcs	43	1/24/2022		yes
100453	ccc	600	1/20/2022		yes
100453	abc	654	1/22/2022		yes
100453	bddb				no
100453	dbfd	0	1/27/2022		yes
100453	qcs	322	1/22/2022	x	yes
100453	gtsg	999	1/24/2022		yes
1004286	attreag	144	1/27/2022		yes
1004286	gtfsgrewtr	0	1/22/2022		yes
1004286	abc	0	1/24/2022		yes
1004286	qcs	176	1/27/2022	x	yes
1004286	ccc	199	1/22/2022		yes
3871978	ccc	376	1/24/2022		yes
3871978	abc	388	1/27/2022		yes
3871978	agragf				no
3871978	fdaegtrag				no
3871978	bddb	312	1/27/2022	x	yes

If there is empty cell with price : Category: no answer, no quote

If there is "0" in cell with price: Category: answered, no quote

If other price relation to "won" price is less than 10%: Category: 0 - 10%

If other price relation to "won" price is between 10% - 30%: Category: 10% - 30%

If other price relation to "won" price is between 30% - 60%: Category: 30% - 60%.

100234	20	1/22/2022		yes	0 - 10%
100234	19	1/24/2022	x	yes	0 - 10%
100234	30	1/27/2022		yes	30% - 60%
100234	25	1/27/2022		no	10% - 30%
100234				no
100234	43	1/24/2022		yes	<60%

Please note, that sometimes I have five or more quotes for the same article.

Thank you in advance for your support. 

[Anonymous · Accepted solution]

Hi @sabseb2225 ,

Please try this calculated column:

Category = 
VAR _order =
    CALCULATE (
        MAX ( 'Table'[Price] ),
        'Table'[won] = "x",
        ALLEXCEPT ( 'Table', 'Table'[Article no.] )
    )
VAR _rate =
    DIVIDE ( 'Table'[Price] - _order, _order )
VAR _result =
    IF (
        'Table'[Price] = 0
            || 'Table'[Price] = BLANK (),
        BLANK (),
        SWITCH (
            TRUE (),
            _rate < 0.1, "0 - 10%",
            _rate >= 0.1
                && _rate < 0.3, "10% - 30%",
            _rate >= 0.3
                && _rate < 0.6, "30% - 60%",
            _rate >= 0.6, ">60%"
        )
    )
RETURN
    _result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[sabseb2225]

Hello Gao, it works! Hats off to you!