Report Table View Issue

[GuestUser]

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

[Icey]

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.

[GuestUser]

Hi @Icey 

is it possible fo u to attach pbix file

i need report with below columns

PnL  level5  leve6 Amount

[Icey]

Hi @GuestUser ,

I used the file downloaded from the post you provided.

Best Regards,

Icey

[GuestUser]

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)

[Icey]

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.

[GuestUser]

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]

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.

[GuestUser]

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]

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.