Create 2 columns based on stage

[fazza1991]

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 creating grouped columns by SWITCh and TRUE and measures for # and £ using similar switch and true.

the problem I'm having it the pipeline stage is not adding up the total including Won and Lost because they already appear in the switch. 

any help on this.

I've exhausted CGPT 4o

[v-yilong-msft]

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]

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

[v-yilong-msft · Accepted solution]

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.

[fazza1991]

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.

StageGroup	Amount
Won	100
Evaluation	200
Negotiation	300
Lost	100

Final Table output

Stage Group	#	£
Pipeline	4	700
Won	1	100
Lost	1	100

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.

[lbendlin]

Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?

[fazza1991]

added bit more info into the reply above. Hopefully this covers what you were after to help me