• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculations In Power Query

Bomino

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

Code:
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

63866

Any help will be greatly appreciated.
Thank you.
 

Bomino

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

Code:
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.
 

Bomino

Member
I went back to the initial idea of converting Sales & Transactions Columns into Lists.
Code:
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
 
Top