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:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Site Name[/TD]
[TD]Category[/TD]
[TD]Period 1[/TD]
[TD]Period 2[/TD]
[TD]Period 2[/TD]
[TD]Period 3[/TD]
[/TR]
[TR]
[TD]Delta[/TD]
[TD]Drink Sales[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Delta[/TD]
[TD]Food Sales[/TD]
[TD]10[/TD]
[TD]5[/TD]
[TD]7.5[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Delta[/TD]
[TD]Drink COS[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Delta[/TD]
[TD]Food COS[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Beta[/TD]
[TD]Drink Sales[/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD]8[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]Beta[/TD]
[TD]Food Sales[/TD]
[TD]8[/TD]
[TD]7[/TD]
[TD]4[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Beta[/TD]
[TD]Drink COS[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Beta[/TD]
[TD]Food COS[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]
















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"