Hi,
I'm not very experienced in using PowerPivot but I have come pretty far by using Google. However, this time around Google can't help me. I'm trying to figure out how I can calculate hitrate based on PowerPivot data.
I have created a sample data set that looks like the one I'm working on. Every month the document is updated with a new data set, which is copied below the data set from the previous month. In the sample file there are data for 13 months. The column 'QRDate' indicates which month the data set belongs to.
I would like to calculate 'Hitrate of the month' as well as '12 months moving average hitrate'. However, I really have no idea where to start (and I don't know if it is even possible to achieve what I'm looking to do).
My hitrate is defined as: SUM(Awarded+Closed)/SUM(Awarded+Closed+Lost) = Hitrate
Is it possible, somehow using PowerPivot to calculate the hitrate automatically? Both the hitrate of the month and the 12 months moving average.
In the sample file on the Hitrate tab you can see have I have calculated the hitrate manually (note the moving average is only three months here due to the sample size). That is my temporary solution. The problem is that I can't slice that as I can with all my other graphs and I need to manually update it every month.
You can find the sample file here.
I'm not very experienced in using PowerPivot but I have come pretty far by using Google. However, this time around Google can't help me. I'm trying to figure out how I can calculate hitrate based on PowerPivot data.
I have created a sample data set that looks like the one I'm working on. Every month the document is updated with a new data set, which is copied below the data set from the previous month. In the sample file there are data for 13 months. The column 'QRDate' indicates which month the data set belongs to.
I would like to calculate 'Hitrate of the month' as well as '12 months moving average hitrate'. However, I really have no idea where to start (and I don't know if it is even possible to achieve what I'm looking to do).
My hitrate is defined as: SUM(Awarded+Closed)/SUM(Awarded+Closed+Lost) = Hitrate
Is it possible, somehow using PowerPivot to calculate the hitrate automatically? Both the hitrate of the month and the 12 months moving average.
In the sample file on the Hitrate tab you can see have I have calculated the hitrate manually (note the moving average is only three months here due to the sample size). That is my temporary solution. The problem is that I can't slice that as I can with all my other graphs and I need to manually update it every month.
You can find the sample file here.
Last edited:
