Forum Discussion

fazza1991's avatar
fazza1991
Helper II
1 year ago
Solved

Create 2 columns based on stage

I would like to create the following table in PowerBI   Stage,#,£ Pipeline,X,Y Won,X,Y Lost,X,Y   where pipeline is count of all stages. won is won stage loat is lost stage   ive tried cre...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi fazza1991 ,

I create a table as you mentioned.

Then I create a calculated column.

StageGroup =
SWITCH (
    TRUE (),
    SalesData[Stage] = "Pipeline", "Pipeline",
    SalesData[Stage] = "Won", "Won",
    SalesData[Stage] = "Lost", "Lost",
    "Other"
)

I also create two measures and here are the DAX codes.

CountStage = COUNTROWS(SalesData)
SumAmount = SUM(SalesData[Amount])

Finally I create a measure to calculate PipelineTotal.

PipelineTotal = 
CALCULATE (
    [SumAmount],
    ALL ( SalesData ),
    SalesData[Stage] IN { "Pipeline", "Won", "Lost" }
)

 

 

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.

fazza1991's avatar
fazza1991
Helper II
1 year ago

I have managed to work it out. I had to do it as a calcuated table. Very long winded. not ideal so if there is a better solution i am up for it

 

StageGroupingTable =
UNION(
SELECTCOLUMNS(
Opportunities,
"Stage_Group", "Pipeline",
"ID", Opportunities[ID],
"Amount", Opportunities[Amount],
"Created_Date", Opportunities[Created_Date],
"Close_Date", Opportunities[Close_Date]
),
SELECTCOLUMNS(
FILTER(Opportunities, Opportunities[Stage] = "Closed Won"),
"Stage_Group", "Won",
"ID", Opportunities[ID],
"Amount", Opportunities[Amount],
"Created_Date", Opportunities[Created_Date],
"Close_Date", Opportunities[Close_Date]
),
SELECTCOLUMNS(
FILTER(Opportunities, Opportunities[Stage] = "Closed Lost"),
"Stage_Group", "Lost",
"ID", Opportunities[ID],
"Amount", Opportunities[Amount],
"Created_Date", Opportunities[Created_Date],
"Close_Date", Opportunities[Close_Date]
)
)

 

Then creating the measures in that group. THe issue was down to the fact it could not recognise the columns accuratley as they were determined by the switch groups annpoyingly there was no dynamic logic behind it

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.