Sorting a Funnel in Power BI on a custom column

[sachingupta31]

Hello,

I am trying to create a Funnel in Power BI. I created a custom column name "StageOrder" which will have below code

StageOrder =
Switch(

TRUE(),
'Table_Name'[column_name] = "New", 1,
'Table_Name'[column_name] = "Marketing Engaged", 2
'Table_Name'[column_name] = "Marketing Qualified", 3,
'Table_Name'[column_name] = "Sales Accepted", 4,
'Table_Name'[column_name] = "Sales Qualified", 5,
'Table_Name'[column_name] = "Opportunity", 6,
'Table_Name'[column_name] = "won", 7,
BLANK()
)

When I see the funnel in the power BI front end report, I try to sort the funnel based on StageOrder column, but that column is not available for sorting. Below is how my funnel is coming up.

I have gone through some of the articles to see if custom sorting is possible in Power BI and tried some of the solutions as well like use the StageOrder column in the tooltip filter. I tried that but that is coming as countOfStageOrder or DistinctCountOfStageOrder or AverageOfStageOrder like that. Below is the link i went through to solve this but did not work for me.

https://community.fabric.microsoft.com/t5/Desktop/Sorting-a-funnel-chart/m-p/3775403

I am not able to sort this funnel based on the stageOrder precedence I gave. Can someone please help me to overcome this issue?

Thanks.

[Anonymous]

Hi, @sachingupta31 

The sorting column in the case you referenced is generated through Power Query:

You can refer to the PBIX in PowerQuery to perform the same operation and obtain this custom sorted funnel.
I will demonstrate the process of creating a calculated column through DAX and then sorting the funnel in the following dataset:
First, I use the following DAX expression to generate a calculated column:

StageOrder = SWITCH(TRUE(),
    'Table'[Category]="A",3,
    'Table'[Category]="B",2,
    'Table'[Category]="C",1,
    'Table'[Category]="E",4,
    'Table'[Category]="D",5)

I then use the following DAX expression to create a calculated column that replicates our Category column to avoid a circular dependency:

Column = 'Table'[Category]

Next, sort the Column column by the StageOrder column:

Use column in the category of the funnel chart and use the value field in values:

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[Anonymous · Accepted solution]

Hi, @sachingupta31 

The sorting column in the case you referenced is generated through Power Query:

You can refer to the PBIX in PowerQuery to perform the same operation and obtain this custom sorted funnel.
I will demonstrate the process of creating a calculated column through DAX and then sorting the funnel in the following dataset:
First, I use the following DAX expression to generate a calculated column:

StageOrder = SWITCH(TRUE(),
    'Table'[Category]="A",3,
    'Table'[Category]="B",2,
    'Table'[Category]="C",1,
    'Table'[Category]="E",4,
    'Table'[Category]="D",5)

I then use the following DAX expression to create a calculated column that replicates our Category column to avoid a circular dependency:

Column = 'Table'[Category]

Next, sort the Column column by the StageOrder column:

Use column in the category of the funnel chart and use the value field in values:

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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