DAX Help Needed: Group values by transaction id and check class

[ketan10]

I have a Table 1 using this I need to create a Table 2 as shown in the image below. I would appreciate any help with the logic on this to be done either using DAX or maybe in Tableau.  Thank you.

Note: If a transaction id has a class 'SP' then for that transaction id replace the class 'WSP' with 'SP' and then get the sum for that transaction id.

[Zubair_Muhammad]

HI @ketan10

Try this Calculated Column

New Class =
IF (
    CALCULATE (
        COUNTROWS ( TableName ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Transaction ID] ),
            TableName[Class] = "SP"
        )
    )
        >= 1,
    "SP",
    TableName[Class]
)

[Zubair_Muhammad · Accepted solution]

@ketan10

This Column for New Revenue

New Revenue =
CALCULATE (
    SUM ( TableName[Revenue] ),
    ALLEXCEPT ( TableName, TableName[Transaction ID] )
)

[ketan10]

Hi @Zubair_Muhammad Thank you. This works perfect !