I’m currently working with a dataset in Quicksight that includes information about warehouses, their sub-inventories, and their respective substorages. Each substorage is associated with a specific item based on the type of substorage the item requires.
My goal is to create a pivot table that displays the warehouse, sub-inventory, and substorage along with item information. One of the measures I want to calculate is the proportion of substorage space that each item occupies (measured in pallets).
The total capacity of the warehouse is the sum of the capacities of all substorages. When a dimension (such as sub-inventory) is collapsed in the pivot table, the table should display the capacity utilization for that sub-inventory. Similarly, if the sub-inventory is collapsed, the table should calculate and display the capacity utilization of the entire warehouse. In essence, the calculated field should be able to compute space utilization at all levels, with subtotals at each level.
However, I’m encountering an issue with calculating the capacity (the denominator in the utilization ratio). I can’t simply sum the substorage capacities at the row level because the substorage is joined to the items and thus repeated. Additionally, I can’t use sum(substorage capacity)/count(substorage capacity) because the substorage capacity varies between different sub-inventories and warehouses.
Could you please assist me in correctly formulating this expression? Any guidance would be greatly appreciated. Thank you.
This is the expression I’m working with but it does not give the correct result for sub-inventory or warehouse levels, only works on the sub storage level.
Hello @Ali_B, thank you for taking the time to provide so much detail, it really helps me understand the issue you are facing.
I believe the best course of action is going to be utilizing LAC-W functions rather than LAC-A functions to complete this task. I think you are on the right track with your initial ifelse statement. I am thinking though that you would want to replace sum() and count() with sumOver() and countOver().
I think it would be worth trying it 2 different ways since it is going to be inside of a pivot table. You could try it first with a partition for Substorage Type and if that doesn’t give the expected output, you can try without. I’ll provide an example that contains the partition, but to exclude it just remove the field in the [] brackets and leave them empty.
Also, if you want a different partition you can switch Substorage Type for another category field, or even add another field. Playing around with these LAC-W functions should lead you to your desired solution, but if you have any further questions please let me know!
In retrospect, it makes sense now that I have written it already, but my God, if you have ever worked with top-tier BI tools like Qlik Sense, working with QuickSight feels like fighting with your data. Most of your expressions look needlessly weird because JOIN does not replace complete data modeling and entity relationship capabilities.
Hello @Ali_B, I am glad you found a solution that works for you. I knew moving towards the LAC-W functions for your calculation would be guide you towards the result you were expecting.
QuickSight definitely takes a little more experience to figure out than some of the other BI tools, but it is also new. Those functionalities will continue to be added over time and it also provides a more custom/open opportunity to build visualizations. We are here to help in that process of learning, so please post a new question in the community any time you run into a new problem. Thank you!
