Month wise Average of Measure

Question

Dear Friends

I am working in hospital and draw a bar graph in OBIEE and applied average function on measure, it was working fine until next month start,

*  In April-2020, total counts are 25 and 20 working days as shown in Fig.1, average must be 1.25
* in March-2020, total counts are 19 and 2 working days as shown in Fig.2, average must be 9.5

but it is calculating accumulative average of measure of all the months which is total count=25+19= 44 and total working days as 20+2= 22 ---> 44/22 =2.0

but i need average of selected month only, please help me ---- month is selected with slider

Function used avg(measure)

Fig.1,  Date wise PCR test and average for the month of April 2020

Fig.2,  Date wise PCR test and average for the month of March 2020

Rai Qaiser Hussain · Answer

Dear @Joel Acha

Earlier I tried AVG(measure BY month) also calculated manually almost 2 or 3 times but everytime calculation was wrong then I tried AVG(measure) even then value was wrong  but when you posted the solution avg(measure by month)  then I tried again and calculated and it is working fine ---- I had implemented your suggested solution yesterday and were trying to find out the mistake which I did, so I could share but couldn't find the mistake

Definitely I was doing something wrong  or I was mentally tired due to current situation of Corona Panademic and load of work ---- Anyway your answer is correct and it is working fine, Thank you very much for your help everytime

Joel Acha · Answer

What is the formula that you're using to calculate your average? You'll need to use something like this:

AVG(measure BY month)