Forum Discussion

vchan's avatar
vchan
Regular Visitor
7 years ago

How to visualize data from a long term plan consisting of multiple spreadsheets

Hi,

 

I am new to both Power BI and this forum, so I apologize in advance if I am asking something obvious.

 

Suppose I have 4 spreadsheet tabs all looking like this:

 

Asset     2019       2020       2021

A             $              $              $

B             $              $              $

C             $              $              $

 

The first tab contains Labor Costs, second contains Material Costs, third contains Admin Costs, and fourth contains EBITDA. The current relationships among the tables are linked only by Asset as the key.

 

Questions:

  1. How could I display in the first chart the trend of the Sum (Labor, Material, Admin) by Asset by year (x-axis), and the trend of EBITDA by Asset by year (x-axis)? [NTD: The objective is to see if Costs are rising faster than EBITDA.]
  2. How could I display in a second chart the 2019-2021 Average (Labor, Material, Admin) by Asset in a pie-chart? And a third chart the 2019-2021 Average EBITDA by Asset in a pie-chart? [NTD: The objective is to see if a certain Asset is responsible for an out-sized Cost or EBITDA.]

Thanks very much in advance for your time.

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    vchan,

    You would need to unpivot columns in each table, and append cost tables in a single table, then create relationship between the appended table and the EBITDA table. For more details about how to create the two charts, please review attached PBIX file.



    Regards,
    Lydia

    • vchan's avatar
      vchan
      Regular Visitor
      7 years ago

      Hi Lydia,

       

      Thank you so much for taking the time to respond to my questions. I was hoping there would be a solution that does not involve "unpivoting" or normalization of the costs tables, and still would allow me to visualize the data in ways similar to the following:

       

      20181011 Sample visualization

       

      where the Costs and EBITDA are the average Costs and average EBITDA of each Asset over 2019-2021. So far, I could only do this by first calculating the 2019-2021 averages into a table. Any instructions that could show me how I could save this first step, perhaps by joining the Costs tables in some ways, would be greatly appreciated. Thanks much in advance.