
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Profit & Loss P&L calculate Gross Profit % GP% using Janelle's model
I am trying to get your solution to work for me, but I am having trouble. When the calculation below is in my straight table, it returns a null value. When the calculation is in a text box by itself it calculates correctly. I tried adding another column so that it would divide each account by Total Sales, but it only worked for the Total Sales line.
Calculation:
if([Exec P&L Heading] <> null(),
if([Exec P&L Level] = 's','',
if([Exec P&L Percent]='p',
num(
sum({$<Month={$(vMaxYTDMonth)},[Account Description]={'Gross Profit'}>} [Actual])
/
sum({$<Month={$(vMaxYTDMonth)},[Exec P&L Heading]={'Total Sales'}>} [Actual])
,'#,##0.0%')
,
if([PL Sign]=1,
num((sum({< [Year]={$(vYTDYear)}, Month={$(vMaxYTDMonth)} >} [Actual])*-1)/1000,'#,##0'),
num(sum({< [Year]={$(vYTDYear)}, Month={$(vMaxYTDMonth)} >} [Actual])/1000,'#,##0')
) ) ) )
As I said above, the " if([Exec P&L Percent]='p', " portion of the formula works as expected in a text box, but not in my straight table.
What am I doing incorrectly? Any suggestions are welcome. Thank you in advance!
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this:
if([Exec P&L Heading] <> null(),
if([Exec P&L Level] = 's','',
if([Exec P&L Percent]='p',
num(
sum(TOTAL {$<Month={$(vMaxYTDMonth)},[Account Description]={'Gross Profit'}>} [Actual])
/
sum(TOTAL {$<Month={$(vMaxYTDMonth)},[Exec P&L Heading]={'Total Sales'}>} [Actual])
,'#,##0.0%')
,
if([PL Sign]=1,
num((sum({< [Year]={$(vYTDYear)}, Month={$(vMaxYTDMonth)} >} [Actual])*-1)/1000,'#,##0'),
num(sum({< [Year]={$(vYTDYear)}, Month={$(vMaxYTDMonth)} >} [Actual])/1000,'#,##0')
)
)
)
)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you provide a sample to play around with?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this:
if([Exec P&L Heading] <> null(),
if([Exec P&L Level] = 's','',
if([Exec P&L Percent]='p',
num(
sum(TOTAL {$<Month={$(vMaxYTDMonth)},[Account Description]={'Gross Profit'}>} [Actual])
/
sum(TOTAL {$<Month={$(vMaxYTDMonth)},[Exec P&L Heading]={'Total Sales'}>} [Actual])
,'#,##0.0%')
,
if([PL Sign]=1,
num((sum({< [Year]={$(vYTDYear)}, Month={$(vMaxYTDMonth)} >} [Actual])*-1)/1000,'#,##0'),
num(sum({< [Year]={$(vYTDYear)}, Month={$(vMaxYTDMonth)} >} [Actual])/1000,'#,##0')
)
)
)
)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
That worked! Thanks @Sunny T!


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello Sunny,
Could you please send me an example model with some sample data for P&L reporting? I liked so much, i'm gonna work on it?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Have you seen this? Below thread seem to come with an example, may be something you can play around with
