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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
GuestUser
Helper V
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
Icey
Community Support
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]
    )
)

pl.PNG

 

 

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

Icey
Community Support
Community Support

Hi @GuestUser ,

 

I used the file downloaded from the post you provided.

 

 

Best Regards,

Icey

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)

sr_format1.jpg

Icey
Community Support
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]
    )
)

pl2.PNG

 

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

Icey
Community Support
Community Support

Hi @GuestUser ,

 

It is suggested to publish your report to Power BI Service and then use "Analyze in Excel".

analyze.PNG

 

 

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!

 

Icey
Community Support
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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors
Top Kudoed Authors