Hi,
I have a project to report number of times an equipment fails and the average time between failures. The failure can be analyzed from the perspective of a plant, a section in a plant, the line, the equipment or the parts in equipment etc. So, the number of times an equipment failed can be different from the number of times a specific part in an equipment fails. Similarly the average time failure for an equipment can be different from the time failure for a specific part in the equipment. For eg if an equipment has 2 parts, Part A and part B, and the equipment failed because of Part B, the analysis should be able to show that the equipment has failed. But when analyzed from perspective of part B, there is no failure.
Please find attached a sample data set which I hope can be used to demonstrate the solution. It is customer sales sample set but the issue is similar. In attached workbook,on sheet pivot1, the pivot should be able to show the number of times and average days interval when the product is sold to the customer. On sheet pivot2, it should show number of times and average days interval when the product is sold to any customer. On sheet pivot3, it should show number of times and average days interval when the product is sold to any customer in the region. On sheet pivot4, it should show number of times and average days interval when when we make a sale to the customer in the region.
I read on the web that I should use index (which I tried) but I do not know DAX enough to tweak it to what I need. Would be grateful for your help. Our dataset is close to 30K rows of downtime log. Also, the power pivot in our Excel version (microsoft 365 build 16) does not have the SELECTEDVALUE function.
Many thanks in advance.
-Cheng
I have a project to report number of times an equipment fails and the average time between failures. The failure can be analyzed from the perspective of a plant, a section in a plant, the line, the equipment or the parts in equipment etc. So, the number of times an equipment failed can be different from the number of times a specific part in an equipment fails. Similarly the average time failure for an equipment can be different from the time failure for a specific part in the equipment. For eg if an equipment has 2 parts, Part A and part B, and the equipment failed because of Part B, the analysis should be able to show that the equipment has failed. But when analyzed from perspective of part B, there is no failure.
Please find attached a sample data set which I hope can be used to demonstrate the solution. It is customer sales sample set but the issue is similar. In attached workbook,on sheet pivot1, the pivot should be able to show the number of times and average days interval when the product is sold to the customer. On sheet pivot2, it should show number of times and average days interval when the product is sold to any customer. On sheet pivot3, it should show number of times and average days interval when the product is sold to any customer in the region. On sheet pivot4, it should show number of times and average days interval when when we make a sale to the customer in the region.
I read on the web that I should use index (which I tried) but I do not know DAX enough to tweak it to what I need. Would be grateful for your help. Our dataset is close to 30K rows of downtime log. Also, the power pivot in our Excel version (microsoft 365 build 16) does not have the SELECTEDVALUE function.
Many thanks in advance.
-Cheng