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