Create 2 columns based on stage

Hi fazza1991 ,

I create a table as you mentioned.

Then I think you can try this DAX code.

Table 2 = 
SELECTCOLUMNS (
    { "Pipeline", "Won", "Lost" },
    "Stage Group", [Value],
    "#",
        SWITCH (
            [Value],
            "Pipeline", COUNTROWS ( 'Table' ),
            "Won", COUNTROWS ( FILTER ( 'Table', 'Table'[StageGroup] = "Won" ) ),
            "Lost", COUNTROWS ( FILTER ( 'Table', 'Table'[StageGroup] = "Lost" ) ),
            BLANK ()
        ),
    "£",
        SWITCH (
            [Value],
            "Pipeline", SUMX ( 'Table', [Amount] ),
            "Won", SUMX ( FILTER ( 'Table', 'Table'[StageGroup] = "Won" ), [Amount] ),
            "Lost", SUMX ( FILTER ( 'Table', 'Table'[StageGroup] = "Lost" ), [Amount] ),
            BLANK ()
        )
)

It will also give you the result you want.

Best Regards

Yilong Zhou

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