Percentage of Revenue

[violet11_6]

Hi,

Any kind soul there who could help me? I am trying to get TotalRevenue to appear on every PnL account on my PnL Statement as follows:

Account Name	Value	Total Revenue
Revenue	1,000	1,000
Cost of Sales	400	1,000
SG&A	300	1,000

I have used the following formula:

TtlRevenue = 
VAR Revenue = CALCULATE(SUM('PnL Database'[Value], Filter('PnLDatabase','PnL Database'[AccountName]="Revenue"))
RETURN CALCULATE(Revenue, ALL('PnL Database'[AccountName]))

and my table looks like this:

Year	Currency	Entity	AccountName	Value
			Revenue
			Cost of Sales
			SG&A

However, I get the following results instead. The TotalRevenue does not appear against every account line.

Account Name	Value	Total Revenue
Revenue	1,000	1,000
Cost of Sales	400
SG&A	300

My ultimate aim, as you can guess, is to work out the AccountNameValue as a Percentage of my TotalRevenue.

Any advice? Thanks in advance for your help.

[amitchandak]

@violet11_6 , Try like

CALCULATE(SUM('PnL Database'[Value], 'PnL Database'[AccountName]="Revenue")

or

CALCULATE(SUM('PnL Database'[Value], Filter(allselected('PnLDatabase'),'PnL Database'[AccountName]="Revenue"))

[violet11_6 · Accepted solution]

Hi,

Thank you. I managed to solve it with the ALL filter. I suppose I was pulling data from the incorrect database.

Thanks

[P_BI_Noob]

Can you share the updated DAX expression? I have this same problem