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