cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Paulyeo11
Impactful Individual
Impactful Individual

How to create PNL filed ?

P&L Question

 

Hi Amit

I have below raw data

 

GL_CODE AMT

1....................10

10..................10

20...................-5

30...................-5

40..............,.,,,2

50................,.,2

60..,,,......,,,,,,,,-3

70...,,,......,,,,,,-,3

 

How should I convert to below by add PNL Field :-

 

GL_CODE AMT  PNL

1....................10.....REV

10..................10.....REV

20...................-5....,.COGS

30...................-5....,.COGS

40..............,.,,,2......OTH_INCOME

50................,.,2......OTH_INCOME

60..,,,......,,,,,,,,-3......EXP

70...,,,......,,,,,,-,3......EXP

 

So that I can get the final result :-

 

REV ..... .............20

COGS ...............10

OTH_INCONE....4

EXP .....................5

NP .......................1

 

Above table use straight table

 

My question is how to recode PNL from GL_CODE ?


Remark :-

 

NP = NET_ PROFIT = REV-COGS+OTH_INCOME-EXP

 

REV +value 

COGS -ve value 

OTH INCOME + ve vakue

EXP -ve value 

 

The actual raw data for REV , GL_CODE is from 1 to 60 . But for example purpose to use 1 to 10

COGS CL_CODE is start from 100 and end 110 , but all run in sequence. 

Paul

 

1 ACCEPTED SOLUTION
Paulyeo11
Impactful Individual
Impactful Individual

Hi AJ

I just test using GROUP for GL_CODE , i notice that this way is very good. because i need only set up once.

Paul

View solution in original post

9 REPLIES 9
Paulyeo11
Impactful Individual
Impactful Individual
aj1973
Resident Rockstar
Resident Rockstar

Hi Paul,

Group and name your Accounts by GL_Code

aj1973_0-1610293067610.png

aj1973_1-1610293254309.png

 

 

Regards
Amine Jerbi
https://www.linkedin.com/in/amine-jerbi-2b3a8928/

Did I answer your question? Mark my post as a solution!
Paulyeo11
Impactful Individual
Impactful Individual

Hi AJ

I just test using GROUP for GL_CODE , i notice that this way is very good. because i need only set up once.

Paul

View solution in original post

Hi @Paulyeo11 ,

So did this issue have been solved? If so, you can accept the suitable reply as solution, that way, other community members could easily find the answer when they get same issues; if not, you can share more details about this issue for further discussion to let us help you better.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Paulyeo11
Impactful Individual
Impactful Individual

Hi Ying

 

I still encounter one more issue on this question , i have posted below link , may be you can take a look.

 

https://community.powerbi.com/t5/Desktop/How-to-move-Rev-from-last-row-to-Top-row/td-p/1595798

 

Paul

 

Paul

Paulyeo11
Impactful Individual
Impactful Individual

Hi AJ

May i know how to move the Rev to the top row and move the COGS to 2nd top row ?

Paulyeo11_0-1610438526530.png

 

Paulyeo11
Impactful Individual
Impactful Individual

Hi AJ

Thsnk you very much , that is what I need . 
But in my actual P&L daw data , my GL Code running number from 1 to 400.

and some time they will adding new GL CODE . 

 

May I know it is possible to use another approach instead of by Grouping methods. 

for example :-

if(GL CODE >1 and <10 , REV)

if(GL CODE >11 and <20 , COGS)

..........

as PNL

 

Paul

 

aj1973
Resident Rockstar
Resident Rockstar

Paul,

Here is a formula you can use but I don't recommend it

Measure 2 = SWITCH(
TRUE(),
SUM(PNL[GL_CODE])<=10,"REV",
SUM(PNL[GL_CODE])>10 && SUM(PNL[GL_CODE])<=30, "COGS",
SUM(PNL[GL_CODE])>30 && SUM(PNL[GL_CODE])<=50, "OTH_INCOME",
"EXP"
)
 
The formula is for calculation use but you won't be able to control and verify when a new account number is added to the group mistakenly. so it's better waste littel more time on grouping the account numbers like I showed you earlier than facing problems down the road.
 
Check out my Financial statements report, I used Grouping and I can detect problems when it happens.
 
 
 
Regards
Amine Jerbi
https://www.linkedin.com/in/amine-jerbi-2b3a8928/

Did I answer your question? Mark my post as a solution!
Paulyeo11
Impactful Individual
Impactful Individual

Hi AJ

Thank you very much for your help. Thank you for sharing with me the link below.

https://app.powerbi.com/view?r=eyJrIjoiMmFhYWVlMDUtYTk3My00Y2U2LTgwMWEtZDQwNjAyNmQwNjc5IiwidCI6IjgzN...

Can share with me the PBI file. as the design is very impressive. 

 

Paul

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.