Forum Discussion

GuestUser's avatar
GuestUser
Icon for Helper V rankHelper V
6 years ago

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 Help
Icey's avatar
Icey
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
GuestUser
Icon for Helper V rankHelper V
6 years ago

Hi Icey 

 

is it possible fo u to attach pbix file

 

i need report with below columns

 

PnL  level5  leve6 Amount

    • GuestUser's avatar
      GuestUser
      Icon for Helper V rankHelper V
      6 years ago

      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's avatar
        Icey
        Icon for Community Support rankCommunity Support
        6 years ago

        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.