Forum Discussion

GuestUser's avatar
GuestUser
Helper 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
Helper 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
    Community 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
      Helper 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

      • Icey's avatar
        Icey
        Community Support
        6 years ago

        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.