Forum Discussion

pedanticpad's avatar
pedanticpad
Helper II
6 years ago
Solved

Financial P&L - How to Layout Data

Hi,

 

I have system driven data laid out like the below;

 

MonthSalesmanInvoice Sales  Direct Consumables  Direct Payroll  etc 
JanX12345    
JanY12346    
JanZ12347    
JanAA12348    
JanAB12349    
Feb      
Mar      

 

 

I may be wrong but to create a P&L matrix table I need the data to be like this;

 

Category Jan  Feb  Mar  Apr 
Sales    
Direct Consumables    
Direct Payroll    
Contribution    
Other Costs    
Other Payroll    
Contribution    
Overheads & Non Direct Payroll    
EBITDA    

 

 

Is there any way to achieve this in power BI?  I have over 5,000 lines each month so I need something with minimal manual intervention.

 

Thanks!

Need Help
  • SivaMani's avatar
    SivaMani
    6 years ago

    pedanticpad,

     

    Yes, It is possible in Power BI. Please do follow the below steps,

    •  Go to Transform Data (aka Query Editor)
    • Select the categories which are plotted as columns like below,

    • Click the Unpivot Columns option (under Transform tab) --Highlighted in the above image.

    • Select Month Column and click the Pivot Column option (under Transform Tab). A dialogue window will appear to choose the value field.

    • Choose your value field and aggregation. Click Ok

     

    Here you go!

     

    Let me know if you have any issues.

     

    Appreciate your kudos!

     

    Regards,

    Siva Mani

     

5 Replies

  • SivaMani's avatar
    SivaMani
    Resident Rockstar
    6 years ago

    pedanticpad,

     

    Yes, It is possible in Power BI. Please do follow the below steps,

    •  Go to Transform Data (aka Query Editor)
    • Select the categories which are plotted as columns like below,

    • Click the Unpivot Columns option (under Transform tab) --Highlighted in the above image.

    • Select Month Column and click the Pivot Column option (under Transform Tab). A dialogue window will appear to choose the value field.

    • Choose your value field and aggregation. Click Ok

     

    Here you go!

     

    Let me know if you have any issues.

     

    Appreciate your kudos!

     

    Regards,

    Siva Mani