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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

## Report Table View Issue

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

9 REPLIES 9
Community Support

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.

Helper V

Hi @Icey

is it possible fo u to attach pbix file

i need report with below columns

PnL  level5  leve6 Amount

Community Support

Hi @GuestUser ,

I used the file downloaded from the post you provided.

Best Regards,

Icey

Helper V

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)

Community Support

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.

Helper V

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

Community Support

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.

Helper V

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!

Community Support

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.

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors