Forum Discussion
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 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
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.
6 Replies
Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?
added bit more info into the reply above. Hopefully this covers what you were after to help me
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.
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.
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
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.
