Showing results for 
Search instead for 
Did you mean: 
Continued Contributor
Continued Contributor

Sorting a column having NULLs

Hi All,


I have a requirement to create below table as matrix in Power BI.



I have two tables in my data model which look like below.



Fact Table:



KPIs table:



The matrix that I have created looks like below:


However, the issue is that I don't know how to sort 'Type' column. Basically, under 'Headcount', Regular should come first and then Non Regular. Similarly for Desk. And under Capacity, the order should be A, Z, E ,B.

How do I achieve this?


Your help would be greatly appreciated.



Community Support
Community Support

Hi @simrantuli ,


First create a column as below:

Column = LOOKUPVALUE('KPIs table'[SO],'KPIs table'[KPI],'Fact Table'[KPI],0)

Then duplicate column KPI,named as _KPI,select _KPI,choosing sort by column:

Screenshot 2020-10-07 130101.png

And you will see:

Screenshot 2020-10-07 130205.png

For the related .pbix file,pls see attached.


Best Regards,

Did I answer your question? Mark my post as a solution!

Hi @v-kelly-msft,


Thanks for your contribution.

Is it possible to sort the column 'Type'? Let's say under 'Capacity', I want the 'Type' to be in the following order: B, A. Z & E.


Best Regards

Simran Tuli

Hi @simrantuli ,


Based on my test,it cant be reached,as the sorting is illogic,I cant find a suitable calculation to achieve it.


Best Regards,

Did I answer your question? Mark my post as a solution!

Community Champion
Community Champion

Hi @simrantuli 

in Power Query add two columns for sorting. Use in tab Column tools button Sort by column (see figure 1 & 2):






With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

Resolver II
Resolver II



is this expected result , to acive this just sort by type in matrix visual


With  Totals 


Super User
Super User

Hi Simran,

Your requirement is not in chronological order. For this custom order you can create one calculated table with the help of Switch and this your type column using that calculated column:

Calculated Column = SWITCH(Table[Type], "Regular", 1,"Not Regular", 2," A", 3,"Z", 4, "E", 5,"B", 6)

Then go to the data view tab ( just above  tab ) then select "Type" Column then choose a "Sort By Column" option from Menu then select the above created custom column (I.e. Calculated Column).


Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , and Fortune 500 Companies Analysis

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors