How to create dynamic columns within Pivot - PowerBI?

[mrichman]

Hi Community, 

Hope you're doing well. Is there any way to create a dynamic column in pivot? I want to rearrage the order... 

Instead of ABC I want to change the order for example to CBA, BAC or any order I would prefer. Thanks in advance!

[DataZoe · Accepted solution]

@mrichman You can change the order by changing the default sort order of the column used for A, B, C. One way I do it is to create a lookup/dimesnion table with a different specified ordering.

One way is to:

1. Go to "Modeling" ribbon, choose "New Table"

2. Enter this DAX expression:

Letters =

DATATABLE("Letter",STRING,"Sort Order",INTEGER,

{

{"A",3},

{"B",2},

{"C",1}

}

)

3. Click on the newly created [Letter] column in 'Letters' table, and in "Column Tools" ribbon choose "Sort by Column", and pick [Sort Order].

4. Join Letters[Letter] to Table[Letter] via relationship in modeling view

5. Now use the new Letters[Letter] in your matrix.

I've attached the PBIX too.

[mrichman]

@DataZoe Thanks for the help!