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
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.

       

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

        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