undefined

[GirishS3]

Want to swap small price with large price and middle price remain same

             Input Table                                                                         Output             
category     Sub_Cat               Price                                                  column
   A                  Small                100                                                    300
   A                  Medium           200                                                     200
   A                   Large              300                                                      100
   B                   Small              200                                                       400
   B                  Medium           300                                                      300
   B                 Large                400                                                       300

[v-zhangti · Accepted solution]

Hi, @GirishS3 

You can try the following methods.

Measure:

Measure = 
Var Large=CALCULATE(MAX('Table'[Price]),ALLEXCEPT('Table','Table'[category]))
Var Small=CALCULATE(MIN('Table'[Price]),ALLEXCEPT('Table','Table'[category]))
Return
SWITCH(TRUE(),
SELECTEDVALUE('Table'[Sub_Cat])="Medium",SUM('Table'[Price]),
SELECTEDVALUE('Table'[Sub_Cat])="Small",Large,
SELECTEDVALUE('Table'[Sub_Cat])="Large",Small )

Column:

Column = 
Var Large=CALCULATE(MAX('Table'[Price]),FILTER(ALL('Table'),[category]=EARLIER('Table'[category])))
Var Small=CALCULATE(MIN('Table'[Price]),FILTER(ALL('Table'),[category]=EARLIER('Table'[category])))
Return
SWITCH(TRUE(),
[Sub_Cat]="Medium",[Price],
[Sub_Cat]="Small",Large,
[Sub_Cat]="Large",Small )

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[MahyarTF]

Hi,

Use below Code to create a column in your table :

_NewPrice =

            if (sheet121[Sub-Cat] = "Small",

                CALCULATE(max(Sheet121[Price]),

                        Sheet121[Sub-Cat] = "Large",

                        ALLEXCEPT(sheet121, Sheet121[Category])

                        ),

                Sheet121[Price])
Appreciate your Kudos

[Fowmy]

@GirishS3 
If you need a DAX solution, add the folloiwng Calculated Column to your table as follows

New Price = 
VAR __SubC = Table1[Sub_Cat]
RETURN 
SWITCH(
    TRUE(),
    __SubC = "Small",
        CALCULATE(
            max(Table1[Price]),
            Table1[Sub_Cat] = "Large",
            ALLEXCEPT(Table1,Table1[Category])
        ),
    __SubC = "Large",
        CALCULATE(
            max(Table1[Price]),
            Table1[Sub_Cat] = "Small",
            ALLEXCEPT(Table1,Table1[Category])
        ),
    Table1[Price]
)