Matrix type calculation for PowerPivot

[chaserracer83]

Good afternoon, I am trying to do a tricky little calculation. It requires filtering values from the current table, and multiplying them by values in another table through a matrix type process. The original formula in Excel uses MMULT. I've tried several things, but nothing accomplishes what I am looking to do...

I have two tables, lets call them "Data" and "Timing". Data contains three fields; Key, Month, and Loss. Timing contains two fields; Month, and Timing Curve.

I need a field in the Data table that does the following:
1) Filters for records in the Data table that have the same Key, a Month less than or equal to the current record, and no Month that is less than the current record Month-20
2) For each record not filtered out from the calculation, the Loss number is multiplied by the Timing Curve from the Timing table. The Timing Curve value that is multiplied depends on how many months have passed since the current record. So for example, imagine a record in the Data table, for a particular key, where month is 10:
The function would take the Loss number for this record and multiply it by Timing Curve at Month 1 (because its been one month since this loss happened).
Then it would take the loss number for month 9 and multiply it by Timing Curve at Month 2
Then it would take the loss number for month 8 and multiply it by Timing Curve at Month 3, and so on...
For a record where month is 11, 10 would be multiplied by Timing Curve at Month 2 because 10 is now one month older
3) After the function has calculated the product of each Loss number with the Timing Curve, it sums up all the values

Hopefully there is an easy solution for this because it has really tripped me up. I have created a sample set of data that can be downloaded at:
https://www.dropbox.com/s/r35bxy7vvvlv1mr/Sample Data.xlsx?dl=0

Any help you are able to offer on this is greatly appreciated.

Thank you,
Chase

 

[ImkeF]

Hi Chase,
you're lucky as you can actually choose between 2 brandnew versions

1) Some pivot-&unpivot acrobatics: https://onedrive.live.com/edit.aspx...FF!74099&parId=DE165DDF5D02DAFF!107&app=Excel
2) A more sophisticated (& documented) version: https://datachant.wordpress.com/201...tiplication-replacing-mmult-with-power-query/

 

[chaserracer83]

ImkeF, thank you for your response. I read the articles you sent, but I am a little confused. Are these using Visual Basic? Do you know if there is a way to do this through PowerPivot?

 

[ImkeF]

Yes, we can do it on our way to the Power Pivot data model. This is M-code which runs through Power Query.
Please have a look at the enclosed file: https://www.dropbox.com/s/bvljk9s2yzs5999/ME_MatrixCalculation.xlsx?dl=0

If this is your desired result:
Key Result
1 160,4951625
2 545,9900768

then we're fine.

Open the Excel-file, choose the Power Query-tab (if you're running Excel-versions before 2016) or Data in Excel 2016. Then click "Show Pane" under "Workbook Queries". To the right you will see a query called "Table3". Rightclick with your mouse on it and choose "edit". To the right in the Query Settings you'll see a number of steps. If you click on them you will see which formulas applied and how the results evolve.

Sure there's a DAX-version for Power Pivot as well, but that's above me unfortunately. We'd have to ask Owen or Matt for that
So please check if the result is correct. If not, please provide desired result in Excel - at least that should give them a good starting point

 

[chaserracer83]

I certainly appreciate the help, but this project has to be done through PowerPivot. I work at a bank and our IT systems are pretty locked down; it literally took 6 months to get PowerPivot pushed to my laptop.

I've rigged up a formula using 20 different "calculate" functions. It actually worked the way I needed it too, but now it's taking up too many resources and my model won't calculate. I'm hoping there is a more streamline way of approaching this within PowerPivot.

I went through and broke out the desired result for a single record in Excel. That can be found in the link below:

https://www.dropbox.com/s/93ch676fsnhfl9a/Sample Data With Answer.xlsx?dl=0

Thank you,
Chase

 

[ImkeF]

Sad, you're really missing out a very valuable tool.

What I did in my solution was to calculate exactly that - but just for the last month per each key, so for 36 for Key 1 for example. So you want to have the value for every month?
How about the months where there won't be 20 months left to go back? Should they return blank?

 

[chaserracer83]

Sounds like it would be helpful. I will request it, but who knows if I will get it this year.

It would need to be calculated for each record. So for example; month 9 would only calculate values for the past 9 months, month 22 would drop some months off. Although, if I could figure out how to do it for the last month I could probably manipulate that for other months.

 

[ImkeF]

This is for the kind DAX-expert who is willing to translate my M-approach into DAX. I've uploaded the workbook here: https://www.dropbox.com/s/bvljk9s2yzs5999/ME_MatrixCalculation.xlsx?dl=0

This is the code:

let
// Start from the data-table
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", Int64.Type}, {"Month", Int64.Type}, {"Loss", Int64.Type}}),
// Do a Crossjoin but reduce the joined rows later
    Merge = Table.NestedJoin(#"Changed Type",{"Key"},#"Changed Type",{"Key"},"Crossjoin",JoinKind.LeftOuter),
// Here we reduce the rows from the crossjoin to the needed month selection
    #"Added Custom" = Table.AddColumn(Merge, "Custom1", each let Mon=[Month] in Table.SelectRows([Crossjoin], each [Month] <= Mon and [Month] >= Mon-20 )),
// Remove the first merge because now we'll work on the filtered one
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Crossjoin"}),
// Now we create the key for the join/merge with the "Timing" table - reverse the month order and create an index which will be the key for the merge called "Combine"
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each Table.AddIndexColumn(Table.Sort([Custom1],{{"Month", Order.Descending}}),"Combine",1,1)),
// again, remove the previous match that isn't needed any more
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Custom1"}),
// now expand the created key "Combine" and other fields
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Month", "Loss", "Combine"}, {"Month.1", "Loss.1", "Combine"}),
// Join with the "Timing" table 
    #"Merged Queries" = Table.NestedJoin(#"Expanded Custom",{"Combine"},Timing,{"Month"},"NewColumn",JoinKind.LeftOuter),
// Expand the "Timing Cure" - the value we need to multiply with
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Timing Curve"}, {"Timing Curve"}),
// Multiply the loss of the month with the value from the Timing curve
    #"Added Custom2" = Table.AddColumn(#"Expanded NewColumn", "Value", each [Timing Curve]*[Loss.1]),
// Aggregate the calculated values (sum) on the required level (orignal table)
    #"Grouped Rows" = Table.Group(#"Added Custom2", {"Key", "Month", "Loss"}, {{"Value", each List.Sum([Value]), type number}})
in
    #"Grouped Rows"

So you need to perform a self-crossjoin whose result you're going to filter to the latest 20 month before the current month. Then create an additional key-column on which you join the timing-table. This key-column is the reverse-order of the months.
Then multiply the value from the timing-table with the value of the matching month (from the crossjoined table).
Aggregate all this to all attributes of the original table which you've started from.

Thanks a lot

 

[chaserracer83]

Thank you very much for all your help. Hopefully I can get this into DAX.

 

[Ozeroth]

Chase/Imke,

Challenge accepted - apologies missed this thread earlier.

It appears you want a calculated column from your original post...

Here is one formula for the calculated column (using table names Data and Timing):

=
SUMX (
    ADDCOLUMNS (
        CALCULATETABLE (
            Data,
            ALLEXCEPT ( Data, Data[Key] ),
            Data[Month] >= EARLIER ( Data[Month] ) - 20,
            Data[Month] <= EARLIER ( Data[Month] )
        ),
        "Months passed", EARLIER ( Data[Month] ) - Data[Month] + 1
    ),
    LOOKUPVALUE ( Timing[Timing Curve], Timing[Month], [Months passed] ) * Data[Loss]
)

And actually you can get away without using ADDCOLUMNS (though it's probably less readable):

=
SUMX (
    CALCULATETABLE (
        Data,
        ALLEXCEPT ( Data, Data[Key] ),
        Data[Month] >= EARLIER ( Data[Month] ) - 20,
        Data[Month] <= EARLIER ( Data[Month] )
    ),
    LOOKUPVALUE (Timing[Timing Curve], Timing[Month], EARLIER ( Data[Month] ) - Data[Month] + 1 )
        * Data[Loss]
)

I've uploaded a copy of Imke's workbook (2013/16 Excel format) with the calculated column added (Data table is called 'Start' in that file).
https://www.dropbox.com/s/mxo4hfa5kwad7ae/ME_MatrixCalculation Owen DAX calc column.xlsx?dl=0

Hope that helps