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 ,
In contination to this thread
https://community.powerbi.com/t5/Service/Profit-and-Loss-Reports/td-p/887396/page/2
i have dragged Level columns from Dimension table (level 5 and Level 6)
I need only one row (total row) for Profit before depreciation..is there any way to achieve it in table view?
In matrix view -- i did and it is working properly (used +/- icon and hidded extra rows) but since download to excel does
not keep the same format, hence we are making it in table view
So can u pls suggest if it is possible? to have only one total row for Profit before depreciation and Profit after
depreciation in table view
Hi @GuestUser ,
Is this what you want?
P&L Schema Amount =
IF (
HASONEVALUE ( 'P&L Schema'[P&L] ),
SUMX (
SUMMARIZE (
'P&L Schema',
'P&L Schema'[P&L],
'P&L Schema'[Account ID],
'P&L Schema'[Weight],
"@Sum Amount", SUM ( 'Fact'[Amount] )
),
[@Sum Amount] * 'P&L Schema'[Weight]
),
SUMX (
FILTER (
SUMMARIZE (
'P&L Schema',
'P&L Schema'[P&L],
'P&L Schema'[Account ID],
'P&L Schema'[Weight],
"@Sum Amount", SUM ( 'Fact'[Amount] )
),
[P&L] = "Profit before depreciation"
|| [P&L] = "Profit after depreciation"
),
[@Sum Amount] * 'P&L Schema'[Weight]
)
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Icey
is it possible fo u to attach pbix file
i need report with below columns
PnL level5 leve6 Amount
Thanks @Icey
but this is not what i am expecting..when i drag level 5 and level 6 columns from dimension table
i can see multiple lines for Profit before depreciation
and profit after depreciation
i need only one total line - mentioning profit before depreciation (amount of 1200)
Hi @GuestUser ,
How about this?
P&L Schema Amount 2 =
IF (
MAX ( 'P&L Schema'[P&L] ) = "Profit before depreciation"
|| MAX ( 'P&L Schema'[P&L] ) = "Profit after depreciation",
IF (
HASONEVALUE ( 'Dimension'[Level5] ),
BLANK (),
SUMX (
SUMMARIZE (
'P&L Schema',
'P&L Schema'[P&L],
'P&L Schema'[Account ID],
'P&L Schema'[Weight],
"@Sum Amount", SUM ( 'Fact'[Amount] )
),
[@Sum Amount] * 'P&L Schema'[Weight]
)
),
SUMX (
SUMMARIZE (
'P&L Schema',
'P&L Schema'[P&L],
'P&L Schema'[Account ID],
'P&L Schema'[Weight],
"@Sum Amount", SUM ( 'Fact'[Amount] )
),
[@Sum Amount] * 'P&L Schema'[Weight]
)
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks @Icey for your inputs !!
Since I need the report in table view , so that when users download..same format of the report is rendered.
But when i change the report layout to table view - it does not show correct result
Hi @GuestUser ,
It is suggested to publish your report to Power BI Service and then use "Analyze in Excel".
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Icey
Actually this report is being prepared on Power BI Report Server where Analyse in Excel option is not there 😞
Hence the need of table view arises!
Hi @GuestUser ,
How about using this: Copy and paste a report visualization?
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
34 | |
30 | |
18 | |
12 | |
8 |
User | Count |
---|---|
50 | |
36 | |
30 | |
15 | |
12 |