Need help combining data

[bartje006]

Hi,

In need help combing some data. I have been trying all morning, but I cannot figure it out. I just started with Power BI, so hopefully there is a simple solution.

I have the following table (file: Excel File😞

What I want is a graph per project with the planned hours per year (bar) and the realised hours per year (line). I want this to be cumulative (so 2013 = 2012+2013, 2014 = 2012 + 2013 + 2014 etc.). And I slicer so I can choose the project to show the graph for. Like this:

 

The problem I am facing is that either Project names or the years end up on the top row/headers. But I want to use both as a filter (not sure that is the right term).

Thanks!

[stretcharm · Accepted solution]

Basically unpivot will help

I posted a similar solution here

http://community.powerbi.com/t5/Desktop/Actual-versus-projected/m-p/348465#M156474

I split the data into 2 querys on both unpivot the years.

Create a key of project and year and then merge back together

Here are the code from the Advanced Queries.

Projects

let
    Source = Excel.Workbook(File.Contents("L:\Downloads\example-file.xlsx"), null, true),
    Blad1_Sheet = Source{[Item="Blad1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Blad1_Sheet,{{"Column1", type text}, {"Column2", type any}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", type any}, {"Column10", Int64.Type}, {"Column11", Int64.Type}, {"Column12", Int64.Type}, {"Column13", type number}, {"Column14", Int64.Type}, {"Column15", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15"}),
    #"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Projectname", type text}, {"Total", Int64.Type}, {"2012", Int64.Type}, {"2013", Int64.Type}, {"2014", Int64.Type}, {"2015", Int64.Type}, {"2016", Int64.Type}, {"2017", Int64.Type}}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type1",5),
    #"Removed Columns1" = Table.RemoveColumns(#"Removed Bottom Rows",{"Total"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns1", {"Projectname"}, "Attribute", "Value"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Columns", "Projectname", "Projectname - Copy"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Attribute", "Attribute - Copy"),
    #"Merged Columns" = Table.CombineColumns(#"Duplicated Column1",{"Projectname - Copy", "Attribute - Copy"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"ProjectYearKey"),
    #"Merged Queries" = Table.NestedJoin(#"Merged Columns",{"ProjectYearKey"},Realised,{"ProjectYearKey"},"Realised",JoinKind.LeftOuter),
    #"Expanded Realised" = Table.ExpandTableColumn(#"Merged Queries", "Realised", {"Value"}, {"Realised.Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Realised",{{"Attribute", "Year"}, {"Value", "Planned"}, {"Realised.Value", "Realised"}})
in
    #"Renamed Columns"

Realised

let
    Source = Excel.Workbook(File.Contents("L:\Downloads\example-file.xlsx"), null, true),
    Blad1_Sheet = Source{[Item="Blad1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Blad1_Sheet,{{"Column1", type text}, {"Column2", type any}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", type any}, {"Column10", Int64.Type}, {"Column11", Int64.Type}, {"Column12", Int64.Type}, {"Column13", type number}, {"Column14", Int64.Type}, {"Column15", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8"}),
    #"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Projectname", type text}, {"Total", type number}, {"2012", Int64.Type}, {"2013", Int64.Type}, {"2014", Int64.Type}, {"2015", type number}, {"2016", Int64.Type}, {"2017", Int64.Type}}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type1",5),
    #"Removed Columns1" = Table.RemoveColumns(#"Removed Bottom Rows",{"Total"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns1", {"Projectname"}, "Attribute", "Value"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Columns", "Projectname", "Projectname - Copy"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Attribute", "Attribute - Copy"),
    #"Merged Columns" = Table.CombineColumns(#"Duplicated Column1",{"Projectname - Copy", "Attribute - Copy"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"ProjectYearKey")
in
    #"Merged Columns"

 

[bartje006]

Thanks! This works.

How can I show the cumulative values for each project?

I tried:

CUMULATIVE = 
CALCULATE (
    SUM ( Hours[Realised] );
    FILTER(
        ALL ( Hours);
        Hours[Year] <= MAX( Hours[Years] )
))

But this shows the total of all projects, instead of only the selected one.

Thanks!

---

Found it: need to use ALLSELECTED instead of ALL.

[stretcharm]

cool.