Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Community Champion
Community Champion

Hi Paul,

Group and name your Accounts by GL_Code

aj1973_0-1610293067610.png

aj1973_1-1610293254309.png

 

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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

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
Community Champion
Community Champion

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

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.