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

Thanks for this.

The 'pipeline' should be from all stages regardless of if its won. So they could be from stages e.g. evaluation, closed won, closed lost etc.

 

StageGroupAmount
Won

100

Evaluation200
Negotiation300
Lost100

 

Final Table output

 

Stage Group#£
Pipeline4700
Won1100
Lost1100

 

I created the following column originally to create the grouped stages.

 

 

 

Stage_Grouping =
SWITCH(
TRUE(),
'Opportunities'[Stage] = "Closed Won", "Won",
'Opportunities'[Stage] = "Closed Lost", "Lost",
'Opportunities'[Stage] IN {"Pre-Pipeline", "Discovery", "Evaluation", "Decision", "Negotiation", "Commitment", "Order Summary", "Order Processing", "Rejected by Sales"}, "Pipeline",
BLANK()
)

This is orignial measure i tried

 

 

 

Opportunities_Count =
VAR PipelineStages =
CALCULATE(
DISTINCTCOUNT(Opportunities[ID]),
Opportunities[Stage] IN {"Pre-Pipeline", "Discovery", "Evaluation", "Decision", "Negotiation", "Commitment", "Order Summary", "Order Processing", "Rejected by Sales", "Closed Won", "Closed Lost"}
)

VAR WonStage =
CALCULATE(
DISTINCTCOUNT(Opportunities[ID]),
Opportunities[Stage] = "Closed Won"
)

VAR LostStage =
CALCULATE(
DISTINCTCOUNT(Opportunities[ID]),
Opportunities[Stage] = "Closed Lost"
)

RETURN
SWITCH(
TRUE(),
MAX(Opportunities[Stage_Grouping]) = "Pipeline", PipelineStages, -- Includes Won and Lost
MAX('Opportunities[Stage_Grouping]') = "Won", WonStage,
MAX('Opportunities[Stage_Grouping]') = "Lost", LostStage
)

But the error that i had was the Total Pipeline was not including all stages included in the seperate switches e.g. The sperate grouping for Won and Lost. But if i removed those out of the stage grouping it would include them in.