P&L Table unable to sort on first column.

[admin11]

Hi All

Many thank to @Icey

help me create a P&L report for below post. 

https://community.powerbi.com/t5/Desktop/How-to-combine-my-Expense-Table-with-my-P-amp-L-Table-into-...

Now my issue is the P&L table first column is not sort correctly. Hope some one can advise me how to do it ? As i have create a P&L table which is able to sort correctly , appear on the left side.

My PBI sample file :-

https://www.dropbox.com/s/b7aku29v8giacil/PBT_V2021_400%20GL_TI.pbix?dl=0

Paul

[v-lionel-msft · Accepted solution]

Hi @admin11 ,

This problem occurs because you did not create a relationship between the two tables, please refer to the file I provided.
(Your .pbxi file is too big, my computer is very difficult to run, so no screenshots are provided.)

Best regards,
Lionel Chen

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

[admin11]

@v-lionel-msft 

Thank you very much , it work fine now.

Paul Yeo

[v-lionel-msft]

Hi @admin11 ,

Please refer to the .pbix file.

Best regards,
Lionel Chen

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

[admin11]

@v-lionel-msft 

i have open and check the file attachment , it work as per i request. when i copy the table to my PBI file , i get the table return all zero.

Very sorry can you explan a bit more detail the step 1 , so that i can understand what you did.

Paul

[v-lionel-msft]

Hi @admin11 ,

Try to do like this.

1, Create a calculated column in 'GL' table.

Column = 
LOOKUPVALUE( GL_Table_EXP[Sort Order], GL_Table_EXP[REC&COGS], GL[REV&COGS&OTH&EXP] )

2, Sort the GL[REV&COGS&OTH&EXP] column by the created column in step1.

Or try this way.

Create a 1:* relationship between 'GL' table and 'GL_Table_EXP' table with GL_Table_EXP[REC&COGS] column and GL[REV&COGS&OTH&EXP] column. And then add the GL_Table_EXP[REC&COGS] (instead of GL[REV&COGS&OTH&EXP]) to the matrix .

Best regards,
Lionel Chen

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

[admin11]

@v-lionel-msft 

Thank you very much for sharing.

For the Option 1 solution. it is possible to share the working PBI file with me , so that i can understand what you mean. As when i create the Column , i get error msg.

Paul