Calculations In Power Query

[Bomino]

Hello,
I have a data set that I grouped and now I would like to calculate the percentage that each Product makes up of the Grand Total in Sales and Transactions. My code ( shown below) is not giving me the desired output.

let
    Source = MyData,
    #"Grouped Rows" = Table.Group(Source, {"Product"}, {{"Sales", each List.Sum([NetValue]), type number}, {"Transactions", each Table.RowCount(_), type number},{"SupplierCount",each List.Count(List.Distinct([SupplierName])),Int64.Type}}),
    Sales = Table.Column(#"Grouped Rows","Sales"),
    TotalSales = List.Sum(Sales),
    Trx =Table.column(#"Grouped Rows","Transactions"),
    TotalTrx = List.Sum(Trx),
    Result=Table.AddColumn(#"Grouped Rows",{{"% Sales",each[Sales]/TotalSales,type number},{"% Transactions",each[Tansactions]/TotalTrx,type number}})
in
    Result

Here is the desired output



Any help will be greatly appreciated.
Thank you.

 

[Chihiro]

It would help if you can upload sample workbook.

But you can see how this sort of calculation is done in...
https://chandoo.org/forum/threads/power-query-of-total.41649/

 

[Bomino]

Thank you Chihiro for your prompt response. I found a solution (a combination of my code and the solution in the thread you've suggested).

let
    Source = MyData,
    #"Grouped Rows" = Table.Group(Source, {"Product"}, {{"Sales", each List.Sum([NetValue]), type number}, {"Transactions", each Table.RowCount(_), type number},{"SupplierCount",each List.Count(List.Distinct([SupplierName])),Int64.Type}}),
    Sales = Table.Column(#"Grouped Rows","Sales"),
    TotalSales  List.Sum(Sales),
    //Trx =Table.column(#"Grouped Rows","Transactions"),
    //TotalTrx = List.Sum(Trx),
    Result=Table.AddColumn(#"Grouped Rows","% Sales",each[Sales]/TotalSales,Percentage.Type),
    
   #"Added Custom" = Table.AddColumn(Result, "TotalTrx", each List.Sum(Result[Transactions])),
   #"Added Custom1" = Table.AddColumn(#"Added Custom", "% Transactions", each [Transactions]/[TotalTrx], Percentage.Type),
   #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"TotalTrx"})
in
    #"Removed Columns"

Thanks.

 

[Chihiro]

You are welcome and thanks for update with your implemented solution

 

[Bomino]

I went back to the initial idea of converting Sales & Transactions Columns into Lists.

let
    Source = MyData,
    #"Grouped Rows" = Table.Group(Source, {"Product"}, 
           {
              {"Sales", each List.Sum([NetValue]), type number},
              {"Transactions", each Table.RowCount(_), type number},
              {"SupplierCount",each List.Count(List.Distinct([SupplierName])),Int64.Type}
           }),

    Sales = Table.Column(#"Grouped Rows","Sales"),
    TotalSales =List.Sum(Sales),

    Result=Table.AddColumn(#"Grouped Rows","% Sales",each[Sales]/TotalSales,Percentage.Type),

    Trx =Table.Column(Result,"Transactions"),
    TotalTrx = List.Sum(Trx),

    SummaryTable = Table.AddColumn(Result,"% Transactions", each [Transactions]/TotalTrx,Percentage.Type)

in
    SummaryTable