Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Profit and Loss

Hello all

New to this forum and indeed DAX & Power BI.

 

Have managed to build a reliable Excel model with Dax Time Intelligence Functionality, however when I import to Power BI, it will now show any P&L line items for calculated lines such as Gross Profit, EBITDA etc, when I sort the header by sort order numbers in a separate table. If I remember back to the Excel Pivot Table Model, I seem to recall that it also didn't sort in the correct order, but I had the ability to drag and drop into the correct sequence. I've tried a custom sort order, and the same thing happens, in that the calculated rows dissapear. Please see the below example of my P&L.

 

Any help you could give would be greatly appreciated, I've got a nagging feeling that it's related to the DAX measures that I've used to calculate the calculated rows.

 

 

 

 

 

Need Help

11 Replies

  • aj1973's avatar
    aj1973
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Anonymous 

    Good thing you have your DAX measures for each row in the Matrix. To sort by your measures you need to store those measures into a calculated table as One column for Index and another column for your measures. use if condition to allign the measures in front of each index. Once done you can sort your measures by the Index.

     

    I myself made a P&L report, however I used different approach. Check it out if you want

    Microsoft Power BI

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Amine

       

      Good work, I looked at your reports and they looked good.

       

      I'm not sure that I totally understand the Index suggestion. I have 13 measures setup for Period actuals, PY same period actuals and Budget. I also have measures for YTD actuals, YTD PY and YTD Budget, so a total of 78 measures. On a matrix, I would like to see the P&L header categories on the left, with these columns to the right of the header.

      • aj1973's avatar
        aj1973
        Icon for Community Champion rankCommunity Champion
        5 years ago

        As I told you in my Report I used different approach. But for you do these steps

        1- Add new Table and generate a series of 13 numbers = to you number of measures

        Table of measures = GENERATESERIES(1 , 13 , 1)
        2- Then Add a column to your table where you use if to store your measures according to order you like

        3- then you can use or order your measures in the visual following the index order

         

  • richbenmintz's avatar
    richbenmintz
    Icon for Resident Rockstar rankResident Rockstar
    5 years ago

    Hi Anonymous,

     

    you need to have a sort by column in your table that defines your P&L header and detail rows. you then assign the sort by column in Power BI Desktop

     

    Thanks,

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks for the quick reply. unfortunately, I've tried this and a custom sort format, i.e Revenues =1, COS = 2 etc, but when sorting, the calculated rows disappear.

      • richbenmintz's avatar
        richbenmintz
        Icon for Resident Rockstar rankResident Rockstar
        5 years ago

        Hi Anonymous ,

         

        Can you provide the pbix?

  • Aryna's avatar
    Aryna
    Icon for Post Partisan rankPost Partisan
    6 months ago

    You could alternatively take a look at a pre-built Power BI Profit & Loss template that already includes financial dashboards and P&L reporting: https://vidi-corp.com/profit-and-loss-dashboards/

     

    It can be a useful reference point and may save a lot of time compared with troubleshooting the full P&L logic and layout manually in Power BI.