Measure calculation for Individual items in Colums

[Ankitsahgal]

Hello everyone,

I am trying to build a P&L that I can slice and dice as required. I get data for multiple sites and need to ability to into with a few clicks.

I have the data in the below format:

Site Name	Category	Period 1	Period 2	Period 2	Period 3
Delta	Drink Sales	5	10	15	20
Delta	Food Sales	10	5	7.5	15
Delta	Drink COS	2	5	3	9
Delta	Food COS	6	1	2	5
Beta	Drink Sales	6	10	8	11
Beta	Food Sales	8	7	4	6
Beta	Drink COS	3	4	2	1
Beta	Food COS	6	4	3	2

<tbody>
</tbody>

I am trying to calculate the Gross profit which is Drink sales - Drink COS , Food sales -Food COS

I have come up the below:

Cost of Sales:= Calculate([Sum of Period 1],[category]="Drink Sales")-Calculate([Sum of Period 1],[category]="Drink COS")

But obviously I will have to create the same measure for all the periods ?

Is there a way to create a single measure as opposed measures for so many periods?

Thank you

Ankit

 

[Matt Allington]

You should first unpivot the data so it looks like this

site. Cat. Period. Value.
A. Cc. 1. 5
A. Cc. 2. 7
etc

thenypu can just put period on your pivot table. Use Power Query to unpivot the data

 

[citizenbh]

Solution in Power Query

Paste in Blank Query in Advanced Editor next code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Site Name", type text}, {"Category", type text}, {"Period 1", Int64.Type}, {"Period 2", Int64.Type}, {"Period 22", type number}, {"Period 3", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Site Name", "Category"}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Attribute", Order.Ascending}, {"Site Name", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Category]), "Category", "Value", List.Sum),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Site Name", "Attribute", "Drink Sales", "Drink COS", "Food COS", "Food Sales"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "Drink Gross", each [Drink Sales]-[Drink COS]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Food Gross", each [Food Sales]-[Food COS]),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom1",{{"Attribute", "Period"}}),
    #"Added Custom2" = Table.AddColumn(#"Renamed Columns1", "Gross Profit", each [Drink Gross]+[Food Gross])
in
    #"Added Custom2"