Forum Discussion
Turn Inventory transaction/movement table into inventory on hand total ie cumulative numbers
Ok, I figured it out. The problem was I had a filter on the visual that I only wanted to show total sales quantity > 0
For whatever reason, that was REALLY dragging the visual down. I assume it was checking every row for quantity > 0 instead of just removing the items where the final total was zero from the report.
Important lesson learned here. I’m loving the performance analyzer, and the ability to inspect the actual query.
I will still put some effort into learning Power Query better as I imagine optizing this stuff will be important.
Thanks so much for the help
Hi,
Thanks for the reply.
I tried doing the Power Query thing, but it's going REALLY slow, on even a very very small subset of data (like 18,000 rows of wht is normally a 28 million row table). Here is my code. Note sure what I'm doing wrong
let
Source = Excel.CurrentWorkbook(){[Name="RawData"]}[Content],
#"Changed Type1" = Table.TransformColumnTypes(Source,{{"Posting Date", type datetime}, {"Item No_", Int64.Type}, {"Item Ledger Entry Quantity", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Posting Date", "Date"}, {"Item No_", "Item"}, {"Item Ledger Entry Quantity", "Quantity"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns" ,{{"Date", type date}, {"Item", type text}, {"Quantity", Int64.Type}}),
#"Added To Date Inventory" =
Table.AddColumn(
#"Changed Type",
"To Date Inventory",
each let
varItem = [Item],
varDate = [Date]
in
List.Sum(
Table.SelectRows(#"Changed Type",each [Item] = varItem and [Date] <= varDate)[Quantity]
),
Int64.Type
)
in
#"Added To Date Inventory"
Here is a PBI file example of what I'm trying to do. To clarify It's not actually the measures that get the inventory balance on hand that is really slowing things down, that runs super quick. But it's when I take the average of the balance on hand that things get REALLY slow.
So when doing this by measures I do
Balance on Hand via Measure = CALCULATE(SUM('Inventory Transactions'[Quantity]), FILTER(ALL(Dates), Dates[Date] <= MAX(Dates[Date])))
followed by
Avg Bal on Hand Via Measure = AVERAGEX(VALUES(Dates[Date]), [Balance on Hand via Measure] )Which I do believe gives me the correct averages based on some Power Pivot verification.
But as mentioned the averages run REALLY slow.
So what I tried to do instead was use SummarizeColums to create an aggregated (and hopefully much smaller) version of the table. Where the balance on hand calculations where already done.
Inventory Summary = SUMMARIZECOLUMNS('Inventory Transactions'[Date] , 'Inventory Transactions'[Item No],
"Inv Bal via Summarize", CALCULATE(SUM('Inventory Transactions'[Quantity]), FILTER(ALL('Inventory Transactions'[Date] ),'Inventory Transactions'[Date] <=MAX( 'Inventory Transactions'[Date] ) ) )
)
Then I did an average
Avg Bal on Hand Via Measure = AVERAGEX(VALUES(Items[Item No]), [Balance on Hand via Measure] )though that doesn't quite get me the correct answer (because the summarize columns is missing the weekend dates). But it's much faster, at least on my desktop. But I get a memory allocation error when trying to refresh on the service.
Hopefully the above is more clearly, I should have written more carefully in the orginal post. My apologies.
Thanks again for the help,
https://drive.google.com/file/d/1kytyZTT6k3qQSW-SJ2lym3PHGNlvzeed/view?usp=sharing
Community ChampionSo for large data sets, Power Query will run slow because it is scanning the entire table. You can partition your data by item first using the techniques here via the Group By operation. The more you can partition your data, the better, so grouping by year and item for example.
As for your DAX, you are using CALCULATE, and I would avoid that if possible. It does someting called context transition, and it can be expensive on a large table.
Consider rewriting the first measure as this (I make no claims this works, I don't have data to validate, and I cannot see your model, and your google drive link requires a login.
Balance on Hand via Measure =
VAR CurrentDate =
MAX( Dates[Date] )
RETURN
SUMX(
FILTER(
ALL( Dates ),
Dates[Date] <= CurrentDate
),
RELATED( 'Inventory Transactions'[Quantity] )
)
Then in the average, I would repeat that measure inside of it vs referring to it, as referring to it puts an implicit CALCULATE() around it.
Sorry I fixed the link, you should be able to download now
https://drive.google.com/file/d/1kytyZTT6k3qQSW-SJ2lym3PHGNlvzeed/view?usp=sharing
The Power query test data set wasn't that large, only 18,000 rows. Also, when it finally finished, it put the final cumulative total on each row. I'm sure I messed the code up somewhere.
I tried inputing the DAX, but it won't pull the related quantity (or related table quantity). There is a relationship, but of course it goes one way from Date table to inventory transaction table.
Ok, I figured it out. The problem was I had a filter on the visual that I only wanted to show total sales quantity > 0
For whatever reason, that was REALLY dragging the visual down. I assume it was checking every row for quantity > 0 instead of just removing the items where the final total was zero from the report.
Important lesson learned here. I’m loving the performance analyzer, and the ability to inspect the actual query.
I will still put some effort into learning Power Query better as I imagine optizing this stuff will be important.
Thanks so much for the help