Problem Statement 3 on GL Analysis :- Sort P&L Field in Sequences

[admin11]

Hi All

https://www.dropbox.com/s/nk4gj83c50jjg7m/PBT%20GL%20VER0029%20GL%20ANALYSIS%20PROBLEM%20STATEMENT%2...

Above my PBI file

Paul

[PhilipTreacy]

@admin11 

Hi Paul,

Sorry, slight modification here.

In Power Query create a new column called SortOrder in the table with this code

if [REV&COGS] = "REV" then 1 else if [REV&COGS] = "COGS" then 2 else if [REV&COGS] = "OTH" then 3 else 4

Which gives you this in the Data Model when you Close & Apply

Then in the Model view click on the REV&COGS column and set the Sort by Column to SortOrder

Regards

Phil

[admin11]

@PhilipTreacy 

thank you very much , i have add the expression for Sort , also set sort at model table , but the table still not yet sort.

Below my PBI file :-

https://www.dropbox.com/s/00v9fzpthv39v2u/PBT%20GL%20VER0030%20GL%20ANALYSIS%20PROBLEM%20STATEMENT%2...

I get error when i try to sort :-

Paul

[PhilipTreacy]

@admin11 

Hi Paul,

Sorry I made a mistake in the initial answer. Check above for the correction.

regards

Phil

[admin11]

Hi Phil

Very sorry , now i get more error.

Can you pls share with me the PBI file.

Paul Yeo

[PhilipTreacy · Accepted solution]

@admin11 

Download PBIX file

OK, because your table was created with DAX I had to modify this code to add the SortOrder column when the table was created

VAR tbl = {(t_rev, 1), (t_cogs, 2), (t_oth, 3) , (t_exp, 4)}

So now you can set the SortOrder in Model view for REV&COGS - I've already done it in the attached PBIX file.

Regards

Phil

[admin11]

@PhilipTreacy 

Thank you so much for your help, now it work fine now.

Paul

[Anonymous]

In power query, you can add an index column and then sort the visual.