Calculate total costs with different components on unpivot table

[Clementw66666]

Hello everyone,

I have a table as per below screenshot. The table is about the costs of three types of products (pizza, pasta and Cola Cola).

I want to have the total costs for all three products by month. However, for Pizza and Pasta, there are only the sub-components for the total costs (Production + Marketing + Branch Store), and for Coca Cola there is already total costs which do not need to do a sum.

In the end I want to show the total costs for Pizza and Pasta as well, then using the total costs to do some analysis; if possible I would also like to keep it's sub-components, so I can create some pie-chart to connect with other charts.

Do you know how this can be done on Power BI? 

Thank you very much in advance!

[bhanu_gautam · Accepted solution]

@Clementw66666 , You can use UNPIVOT in power query then

In Power BI, go to the "Modeling" tab and create a new calculated column using the following DAX formula:

Total Costs =
IF(
'Table'[Type] = "Total Costs",
'Table'[Value],
CALCULATE(
SUM('Table'[Value]),
FILTER(
'Table',
'Table'[Category] = EARLIER('Table'[Category]) &&
'Table'[Date] = EARLIER('Table'[Date])
)
)
)

Total Costs by Month: Use a line chart or bar chart to show the total costs for each product by month.
Sub-Components Breakdown: Use a pie chart to show the breakdown of sub-components for Pizza and Pasta.

[Clementw66666]

Thank you very much!! It works.