Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to Solution.
Hi,
Thank you. I managed to solve it with the ALL filter. I suppose I was pulling data from the incorrect database.
Thanks
@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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
87 | |
84 | |
68 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |