CREATING NEW calculated row

[Anonymous]

Hello,

I would like to regroup all the Product Group name with VMS ( from 29.27- 29.39) such that it returns the line in red .

Best regards ,

Anis 

Product Group Name	COGS (OM) raw materials	COGS (purchased third party)	COGS (OM) packaging materials
02.67 NIN Functional Formulas	148195		31096
02.99 Other infant products	123212		1000
29.12 Devices	-148187	3234	67222
29.24 Vitaflo	1342111	70909516	6323705
29.25 Medical Nutrition	8424456745	31231	4123
29.26 Consumer Care	212323123	1958429041	1399941479
29.27 VMS - Garden of Life	12323123	23413421341324	611675147
29.28 VMS - Professional	4295982588	134134138768	236178859
29.29 VMS - Other	12342341	1675256107	23123
29.30 VMS - Wobenzym	748126387	50447404	14680326
29.31 VMS - VP	6310291451	34234	132151221
29.32 VMS - Nuun	2342	7886757	12313
29.33 VMS Natures’s Bounty	5593292169	8675
29.34 VMS Solgar	3423423	123
29.35 VMS Puritan’s Pride	1473203499	464567467
29.36 VMS – Osteo Biflex	34234	614946
29.37 VMS Private Label (CMO)	342342	13145074
29.38 VMS – Sundown	375380963	123132
29.39 VMS - TBCo Other	2342342	234234
29.40 Pharma	616356225	598417437	73174
29.41 VMHS – Orgain	432365745	343234
29.99 Other HealthCare nutrition	17594487373	3881259048	3133085229
33.03 Pharmaceuticals	134134234	30393899
33.04 Diagnostics	28225418
35.05 Maternal Nutrition WIN	40000	2342342
98.99 Unallocated	12341234
	SUM ALL VMS RAW	SUM ALL VMS PURCHASED THIRD	SUM ALL VMS PACK
CREATED ROW : TOTAL VMS	18827087204	23549767798245	994720988,4

[Anonymous]

Thank you so much !

[Anonymous · Accepted solution]

Hi @Anonymous ,

We cannot create calculated row directly, please refer to my pbix file to see if it helps you.

Create three measures.

package =
VAR _all =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Product] >= 29.27
                && 'Table'[Product] <= 29.39
        ),
        'Table'[COGS (OM) packaging materials]
    )
RETURN
    IF (
        HASONEVALUE ( 'Table'[Product] ),
        MAX ( 'Table'[COGS (OM) packaging materials] ),
        _all
    )

purchased =
VAR _all =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Product] >= 29.27
                && 'Table'[Product] <= 29.39
        ),
        'Table'[COGS (purchased third party)]
    )
RETURN
    IF (
        HASONEVALUE ( 'Table'[Product] ),
        MAX ( 'Table'[COGS (purchased third party)] ),
        _all
    )

raw =
VAR _all =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Product] >= 29.27
                && 'Table'[Product] <= 29.39
        ),
        'Table'[COGS (OM) raw materials]
    )
RETURN
    IF (
        HASONEVALUE ( 'Table'[Product] ),
        MAX ( 'Table'[COGS (OM) raw materials] ),
        _all
    )

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.