Sorting a column having NULLs

[simrantuli]

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.

Thanks!

[v-kelly-msft]

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:

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[simrantuli]

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

[v-kelly-msft]

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,
Kelly

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

[FrankAT]

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)

[sreenub]

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

[sreenub]

With  Totals 

[Tahreem24]

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).