Custom Sort on calculated column based on measure

[Anonymous]

Hi Team,

I am new to to Power BI and need help in custom sort on calculated column.

I have calculated column as 

WOS = IF( [Avaialble_WOS] < 0 , "StockOut" , if ( [Avaialble_WOS] < 2 , "0 to 1.9", if ( [Avaialble_WOS] < 4 , "2 to 3.9", if ( [Avaialble_WOS] < 6 , "4 to 5.9", if( [Avaialble_WOS] < 8 , "6 to 7.9", if ( [Avaialble_WOS] < 10 , "8 to 9.9" , if([Avaialble_WOS] < 15, "10 to 14.9", IF([Avaialble_WOS] > 15, "15+","No Forecast") )))))))

Measures on which it is based:

Avaialble_WOS = IF( [Forecast_Weekly_Rate] = 0 , 99 , ((SUM('Sync WOS Query'[Unrestricted Stock])-SUM('Sync WOS Query'[WOS_Total Backlog]) )/6))

Forecast_Weekly_Rate =

(SUM('Sync WOS Query'[Total Forecast]) - SUM('Sync WOS Query'[MTD Shipments]))/6

Output of column as 

15+

o to 1.9

11 to 12.9

2 to 3.9....

Stockout

I want :

Stockout

0 to 1.9

2 to 3.9..

15+

going through blogs found created table for order only, so i did that but when trying to create relation with WOS column, error circular dependency with WOS.

Please suggest.

Regards,

MN

[Anonymous]

---

@Anonymous wrote:

Hi Team,

I am new to to Power BI and need help in custom sort on calculated column.

I have calculated column as 

WOS = IF( [Avaialble_WOS] < 0 , "StockOut" , if ( [Avaialble_WOS] < 2 , "0 to 1.9", if ( [Avaialble_WOS] < 4 , "2 to 3.9", if ( [Avaialble_WOS] < 6 , "4 to 5.9", if( [Avaialble_WOS] < 8 , "6 to 7.9", if ( [Avaialble_WOS] < 10 , "8 to 9.9" , if([Avaialble_WOS] < 15, "10 to 14.9", IF([Avaialble_WOS] > 15, "15+","No Forecast") )))))))

Measures on which it is based:

Avaialble_WOS = IF( [Forecast_Weekly_Rate] = 0 , 99 , ((SUM('Sync WOS Query'[Unrestricted Stock])-SUM('Sync WOS Query'[WOS_Total Backlog]) )/6))

Forecast_Weekly_Rate =

(SUM('Sync WOS Query'[Total Forecast]) - SUM('Sync WOS Query'[MTD Shipments]))/6

Output of column as 

15+

o to 1.9

11 to 12.9

2 to 3.9....

Stockout

I want :

Stockout

0 to 1.9

2 to 3.9..

15+

going through blogs found created table for order only, so i did that but when trying to create relation with WOS column, error circular dependency with WOS.

Please suggest.

I have removed calculated column based on measure and used variables to defin if condition. it has let me create separate table only for order and then created creation ship between both .this allowed sort by index column in newly created table.

However , i have 4 columns with such range bucket as 0 to 2, 2 to 4 etc. Do i need to create separate tbale for each and then join with original for sortin.

all 4 columns has same range interval, is there a optimized way. thanks

Regards,

MN

---

[amitchandak]

@Anonymous , not very clear. You need to have a sort column.  and mark that as sort column

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

[Anonymous]

Thank you Amit , Please suggest. I have removed calculated column based on measure and used variables to defin if condition. it has let me create separate table only for order and then created creation ship between both .this allowed sort by index column in newly created table. However , i have 4 columns with such range bucket as 0 to 2, 2 to 4 etc. Do i need to create separate tbale for each and then join with original for sortin. all 4 columns has same range interval, is there a optimized way. thanks Regards, MN

[Anonymous · Accepted solution]

@Anonymous 

What exact format of you data column? If you have 2 digits number like the original post, you could take out the first 2 string, and sort by it.

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.