Get a summary table from transaction table

JasMack

New Member
Joined
Apr 14, 2023
Messages
1
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Hi everyone,

I am new here.

I have a table which looks like this.

TeamPersonPeriodCostRevenue
ATom1100200
ATom2150100
AJack15090
AJack27580
BSarah1300400
BSarah2150500

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

ParticularPrior Month (Period <2)Year to date
Cost450825
Revenue6901370

Thanks.

Jason
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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)

Power Query:
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.
1681487753732.png


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.
1681487860471.png


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:
1681488104518.png

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


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

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


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).

1681488404977.png


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


And you get the result:
1681488470434.png


Hope this helps.
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,215
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top