Order attributes

[Msampedro]

Hello,

I have the following table "PnL Structure" (not sure if I can consider it as a pure table since doe not appear in the Table view) and I need to give and order to each attribute, in order to later on use a Matrix and give an specific order to each one.

Below the order I need:

if [Attribute] = "Qty (Liter)" then 1
else if [Attribute] = "Qty (KG+EA)" then 2
else if [Attribute] = "ASP, EUR/lt" then 3
else if [Attribute] = "ASP, EUR/KG+EA" then 4
else if [Attribute] = "Net Sales" then 5
else if [Attribute] = "Variable COGS" then 6
else if [Attribute] = "Vcogs, EUR/lt" then 7
else if [Attribute] = "Vcogs, EUR/KG+EA" then 8
else if [Attribute] = "Var FPDE" then 9
else if [Attribute] = "Var.Contribution" then 10
else if [Attribute] = "F-COGS" then 11
else if [Attribute] = "Fcogs, EUR/lt" then 12
else if [Attribute] = "Fcogs, EUR/KG+EA" then 13
else if [Attribute] = "Gross Profit" then 14
else 15)

Thanks,

Miguel

[danextian]

Hi @Msampedro 

What do you mean by "not sure if I can consider it as a pure table since doe not appear in the Table view"? DATATABLE generates a calculated table and should appear in the Table/Data view.  You can actually add an extra column using the same calc table. 

Example:

DATATABLE (
    "Attribute", STRING,
    "Sort", INTEGER,
    {
        { "Qty (Liter)", 1 },
        { "Qty ((KG+EA)", 2 }
    }
)

But I would very much create this table in Excel, and import it into the model using Enter Data.

[Msampedro]

Hi @danextian ,

I have tried, but not working.

[danextian · Accepted solution]

DATATABLE returns a table and not a column. You need to modify the table itself and not create a calculated column. Note: The code i gave you was just  a sample which of course didn't include all attribute values.

[Msampedro]

Thanks, Finally it was quite easy... just about to edit Datatable formula.... thanks to help me understand the logic.

PnL Structure =
DATATABLE (
"Attribute", STRING,
"Order", INTEGER,
{
{"Qty (Liter)", 1},
{"Qty (KG+EA)", 2},
{"Net Sales", 3},
{"ASP, EUR/KG+EA", 4},
{"ASP, EUR/lt", 5},
{"Vcogs, EUR/lt", 6},
{"Variable COGS", 7},
{"Vcogs, EUR/KG+EA", 8},
{"Var FPDE", 9},
{"Var.Contribution", 10},
{"Fcogs, EUR/KG+EA", 11},
{"Fcogs, EUR/lt", 12},
{"F-COGS", 13},
{"Gross Profit", 14},
{"GM %", 15},
{"VM %", 16}
}
)

[Msampedro]

Hi @Ritaf1983 ,

Thanks for your help.  You are totally right on your understanding.

I have tried Field Parameters, but I think it is not working as after creating Field parameters I do not have each indivdual attribute to give them an order.

Thank you,

Miguel

[Ritaf1983]

Hi @Msampedro 

If I understood your goal correctly, you’re trying to assign a specific order to a set of attributes that will later be used as columns in a Matrix visual.

In order to apply a custom sort order to columns in a Matrix, you’ll need a physical sort column, which isn’t possible with a calculated measure or just a SWITCH expression alone.

You have two main options:

1. Create a disconnected table using "Enter Data"
Include the desired attribute values and a numeric column for the order (1, 2, 3…).
Then, use a SWITCH-based dynamic measure that returns the appropriate value based on the selected attribute.

2. Use Field Parameters (recommended if you're on a recent Power BI version)
Field Parameters allow you to define the attribute order directly, and the Matrix will reflect that order as you set it.

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