• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

DAX formula to recalculate average duration between transactions depending on pivot row context

JingFung

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

Attachments

  • TESTMBTF.xlsx
    205.7 KB · Views: 4
Not sure if your data is really representative of your issue.

Typically speaking this type of calculation requires one of following.
1. Add calculated column, either at Power Query stage or at DAX. Then use the calculated column for down stream calculation.
2. Add table variable to your DAX. To provide evaluation context.

You can see similar concept I posted in reddit in link below.
 
Hi Chihiro,

Thank you for looking into this.


The link gives an idea of what I am trying to calculate but I would like it to be able to calculate depending on my pivot row fields ie. ie not at the individual transaction level eg if I only show customer, how does it accumulate and calculate the average days in between for the customer?

Also, the formula uses SELECTEDVALUE which is not in my Excel version. I tried to replace with Values and HASONEVALUE but it did not work.

Thank you!
 
Back
Top