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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
violet11_6
Regular Visitor

Percentage of Revenue

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 NameValueTotal Revenue
Revenue1,0001,000
Cost of Sales4001,000
SG&A3001,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:

YearCurrencyEntityAccountNameValue
   Revenue 
   Cost of Sales 
   SG&A 


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

Account NameValueTotal Revenue
Revenue1,0001,000
Cost of Sales400 
SG&A300 


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.

1 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

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@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"))

Hi,

 

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

Thanks

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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