Performing a Calculation with a Roll-Up table as denominator
Looking for some advice / best practices on how to accomplish something that I've run into 2 or 3 times and I've had a workaround each time but its probably not the best solution. This occurs when I have a table which I have to perform division logic based on the SUM data of a log file but a single denominator value based on a join from a summary table.
For example, lets assume I'm trying to do an industrial dashboard and I have X pieces of manufacturing equipment that is available a specific amount of time each day (but varies by equipment). This equipment manufactuers widgets that take a variable amount of time to produce, and there is a column in my data set that tells me how much each widget takes to produce from a log file type format. So to Illustrate, the base data set I have is below:
Widgets Produced (LOG)
Machine Available Time Table
So to get my utilization for Machine #21 is this example, I would do the following calculation: (90+37+41) / 727 = That equipment utilization (When converted to percent).
What ultimately ends up happening is if I Merge these on Machine \ Equipment # in Si sense for filtering purposes, my data duplicates. I assume because SiSense is joining the Total Time Available from Table 2, to each row of my log file. I can fix this by doing my calculation based on a MIN(Time Available Seconds) to pull only one available time in my calculation as the denominator instead of my SUM, but that only works if I"m looking at a single machine. If I'm looking at multiple machines, this does not work.
The only work around I've found for this is to sum up data in my Available Time Table and create a new table that contains data similar to the following. This works but the downside of this is I cannot utilize any filters from my FACT table, and essentially have to create this on a sesperate dashboard.
Anyone have ideas on a better way to accomplish this type of calculation, and ideally still be able to use my fact table (from log file) filters?
Please sign in to leave a comment.