• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Column Sorting in Matrix /

S

ShanShami

Member
Hello Everybody,
I am doing one report in Power BI. Need your advise for below.
I am using Matrix visual

1. Sorting Highest to lowest percentage based on Total as shown in below screenshot
2. To show "YW" always in second column - if values not available then should be blank
3. To show highest 5 based on Total percentage excluding "YW" i.e. starting from "CB"

I know how to do it in excel but struggling to do it in Power BI

ODYWCBTQQWASERASERED
LHR-PAR0.4%39.0%25.4%16.3%5.7%3.9%2.9%1.9%0.9%
PAR-LHR0.5%43.6%21.7%16.3%3.8%3.2%2.2%1.2%0.2%
Total0.5%41.3%23.6%16.3%4.7%3.6%2.6%1.6%0.6%

Regards,
 
I don't use PBI but I do know that PBI can use PQ. Here is the Mcode that matches your expectations

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Sorted Rows" = Table.Sort(#"Transposed Table",{{"Column3", Order.Descending}}),
    #"Transposed Table1" = Table.Transpose(#"Sorted Rows"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Reordered Columns" = Table.ReorderColumns(#"Promoted Headers",{"OD", "YW", "CB", "TQ", "QW", "AS", "ER", "AS2", "ER3", "ED"})
in
    #"Reordered Columns"
 
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.
 
@ShanShami ,

To sort a matrix, you can use the sort by column feature on the column. You can hide this column from the consumer side.
1702810053711.png
Then whenever you use this field in a visualization, it's always sorted according to this sort column. That sort column is a numerical field.
In your case as you have a specific sort order requirement, that column should hold a measure. I'm not 100% sure it can be used as a sort column.

It might even be possible with a measure that creates the visual.
 
Last edited:
AlanSidman said:
I don't use PBI but I do know that PBI can use PQ. Here is the Mcode that matches your expectations

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Sorted Rows" = Table.Sort(#"Transposed Table",{{"Column3", Order.Descending}}),
    #"Transposed Table1" = Table.Transpose(#"Sorted Rows"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Reordered Columns" = Table.ReorderColumns(#"Promoted Headers",{"OD", "YW", "CB", "TQ", "QW", "AS", "ER", "AS2", "ER3", "ED"})
in
    #"Reordered Columns"
Click to expand...
Indeed @Allen, the sorting in the column works in PQ, but once the table is loaded in the data model (per default in PBI), the DAX rules, ahum, rule. ;-)
Alphabetical sorting is always A-Z/Z-A.
So there is a measure "OD" and there is a dimension table with a column holding the variables: YW ; CB ; TQ ; QW ; AS ; ER ; AS ; ER ; ED.
Sorting these in a specific order is easy. Once can use a column in PQ that's like
Code: 
if [Dim_Col] = "YW" then 0 else List.PositionOf(List.Sort(List.Distinct(Source[Dim_Col])), [Dim_Col])+1
Would create a sort order that's always YW first, followed by the rest ordered alphabetically.

The challenge here is that is needs to be dynamic based on the OD%. Since that's the result of the measure, the sorting needs to be done in DAX using functions.
Without knowing the data model, it's very hard for me to guess what that sorting formula looks like. I'm not Chihiro :-D.
 
AlanSidman said:
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.
Click to expand...
Thank you. I will go through these links
 
GraH - Guido said:
Indeed @Allen, the sorting in the column works in PQ, but once the table is loaded in the data model (per default in PBI), the DAX rules, ahum, rule. ;-)
Alphabetical sorting is always A-Z/Z-A.
So there is a measure "OD" and there is a dimension table with a column holding the variables: YW ; CB ; TQ ; QW ; AS ; ER ; AS ; ER ; ED.
Sorting these in a specific order is easy. Once can use a column in PQ that's like
Code: 
if [Dim_Col] = "YW" then 0 else List.PositionOf(List.Sort(List.Distinct(Source[Dim_Col])), [Dim_Col])+1
Would create a sort order that's always YW first, followed by the rest ordered alphabetically.

The challenge here is that is needs to be dynamic based on the OD%. Since that's the result of the measure, the sorting needs to be done in DAX using functions.
Without knowing the data model, it's very hard for me to guess what that sorting formula looks like. I'm not Chihiro :-D.
Click to expand...
Thank you. Yes.. the data should be sorted based on dynamic OD% and also, I need to consider top 5 only :)
 
ShanShami said:
Thank you. Yes.. the data should be sorted based on dynamic OD% and also, I need to consider top 5 only :)
Click to expand...
TopN ( 5 ) only is a setting in the visual. I do not have access to my pc right now, so I can't share a screenshot. It is much alike a pivot in excel.
 
This might be an inspiration
community.fabric.microsoft.com

Re: how to sort columns in a matrix visual

Hi , According to your description, if you want to sort the columns in the matrix based on rank, you need to put the rank into the matrix; otherwise it is sorted based on the value against row or value. Amount_filter = sum(FactInternetSales[SalesAmount])Rank = IF ( HASONEVALUE (...
community.fabric.microsoft.com community.fabric.microsoft.com
 
You must log in or register to reply here.
Back
Top