Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Row by row sumifs excel replicate

Dear BI gurus,   I've refered to almost all of old threads in the forum but to no avail. I've been stucking for weeks now.   I am pretty sure it can be done but it is so challenging to replicate ...
  • stretcharm's avatar
    stretcharm
    8 years ago

    I started to look at options in M

     

    These are my initial working, however it may still have some circular reference issues.

    However I've learnt how to reference different rows.

    http://excel-inside.pro/blog/2015/11/05/absolute-and-relative-references-in-power-query/

     

    let
    Source = Excel.Workbook(File.Contents("L:\Downloads\P Bi example.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Exp_site", type text}, {"Helper1", type text}, {"Exp_highstock", Int64.Type}, {"SKU", type text}, {"Imp_site", type text}, {"Helper2", type text}, {"Imp_Demand", Int64.Type}, {"Min Exp & Imp", Int64.Type}, {"Final Allocation", Int64.Type}, {"Exp_remainder", Int64.Type}, {"Imp_remainder", type any}, {"Explaination", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Min Exp & Imp", "Final Allocation", "Exp_remainder", "Imp_remainder", "Explaination"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Index",{{"Index", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Exp_site] <> null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows" , "MinExp&Imp", each List.Min({[Exp_highstock],[Imp_Demand]})),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "FinalAllocation", each if [Index] = 0 then [#"MinExp&Imp"] else 0),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "ExpRemainder", 
        each if [Index] = 0 then [Exp_highstock]-[FinalAllocation] else 0),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "ImpRemainder", each if [Index] = 0 then [Imp_Demand] else 0),
    #"Invoked Custom Function" = Table.AddColumn(#"Added Conditional Column2", "FuncRes", each fnSumIF([SKU], [Index],  #"Added Conditional Column2")),
    #"Added Custom2" = Table.AddColumn(#"Invoked Custom Function", "RefRelative", each Source{[Index]-1}[Index])
in
    #"Added Custom2"

    and a function calld fnSumIF

    let
    Source = (FilterSku as text, FilterRow as number, FilterSource as table) => let
    FilteredData = Table.SelectRows(FilterSource , each ([Index]<FilterRow )),
    #"Grouped Rows" =  Table.Group(FilteredData , {"SKU"}, {{"Sum", each List.Sum([Exp_highstock]), type number}}),
    Result = #"Grouped Rows"{[SKU=FilterSku]}[Sum]
    in
        Result 
in
    Source
stretcharm's avatar
stretcharm
Icon for Memorable Member rankMemorable Member
8 years ago

I've not fully digested your problem, but here are a couple of things that may help.

 

First be careful just reproducting a excel solution as powerbi models are very different and it may be better to redesign the structure of your data into tables.

Not sure how much you know about powerbi models. This video is a good starting point if your new to it.

https://www.youtube.com/watch?v=pvIVMEFQokE

 

M is not so strong on aggreations, but it does have group and sum functions. The IF part could be done with new conditional columns before grouping.

 

However I would probably try and do it in DAX using the SWITCH function for the IF part and the you can aggregate the results.

Some examples.

https://powerpivotpro.com/2012/06/dax-making-the-case-for-switch/

 

I like to use  SWITCH(TRUE(),

followed by a series of true/false conditions.

 

I've also used the New Table option under the modeling tab to create a summarised table based on Sums of other calculated columns such as Swtch expressions. To do this I used the AddColumns Dax Function https://msdn.microsoft.com/en-us/library/gg492204.aspx

 

Hope this gets you going in the right direction.