Get a summary table from transaction table

[JasMack]

Hi everyone,

I am new here.

I have a table which looks like this.

Team	Person	Period	Cost	Revenue
A	Tom	1	100	200
A	Tom	2	150	100
A	Jack	1	50	90
A	Jack	2	75	80
B	Sarah	1	300	400
B	Sarah	2	150	500

Can you please help me with the achieving the following results on Power BI desktop using the table above?

Particular	Prior Month (Period <2)	Year to date
Cost	450	825
Revenue	690	1370

Thanks.

Jason

 

[smozgur]

Welcome to the MrExcel Message Board!

Here is how I would get the result by using M code:
(Assumption: The table is a range in an Excel workbook - C:\somewhere\data.xlsx)

let
    Source = Excel.Workbook(File.Contents("C:\somewhere\data.xlsx"), null, true),
    Table1 = Source{[Item="Table1",Kind="Table"]}[Data],
    ChangeType = Table.TransformColumnTypes(Table1,{{"Team", type text}, {"Person", type text}, {"Period", Int64.Type}, {"Cost", Int64.Type}, {"Revenue", Int64.Type}}),
    CostYTD = List.Sum(ChangeType[Cost]),
    RevenueYTD = List.Sum(ChangeType[Revenue]),

    Filtered = Table.SelectRows(ChangeType, each [Period] < 2),
    Cost = List.Sum( Filtered[Cost]),
    Revenue = List.Sum( Filtered[Revenue]),

    Result = #table({"Particular", "Prior Month (Period <2)", "Year to date"}, 
                    {
                        {"Cost", Cost, CostYTD}, 
                        {"Revenue", Revenue, RevenueYTD}
                    }
                )
in
    Result

And it could also be done by using the UI:

1- Load the table, and add a conditional column as shown below.
If the Period is less than 2 then "Prior Month (Period <2)", otherwise "Year to date".
Click OK.

2- Select the new Custom column and click Group By by using the following settings (need to click on Advanced).
Basically aggregating Cost and Revenue columns. Note that I am also using the same column names as New Column Names.
Click OK.

3- Select the Cost and Revenue columns and right-click on the selection and Unpivot columns (or use the same button on the ribbon Transform tab).
You'll get the following:

4- Select the Attribute column, right-click on it and ribbon->Transform tab->Pivot column with the following settings:
Click OK.

You'll get the following, but Year to date is not correct. We need to get the total:

5- Add a custom column and set the formula as shown below:
Click OK.

6- Remove the wrong Year to date column (actually, this column might have been named something else at the beginning but not a big deal).

7- Rename the Custom and Attribute column as you wish:

And you get the result:

Hope this helps.