Forum Discussion
CREATING NEW calculated row
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 |
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.
2 Replies
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.