Forum Discussion

LeoLi's avatar
LeoLi
Frequent Visitor
1 year ago

Monthly average excludes small volume day

Hello everyone.

 

I am building a dashboard with a matrix to show what how many errors occur each day in a month. I would like to calculate average errors each day in a month without counting those days that total error numbers is below 10 (which is basically weekends) but still count them in total calculations.

 

Below are the measure and table i use.

 

DimDate = ADDCOLUMNS(CALENDARAUTO(),"YearMonth",YEAR([Date]) * 100 + MONTH([Date]),"MonthNum",MONTH([Date]),"Month",FORMAT([Date],"MMMM"),"Day",DAY([Date]),"DayKey",FORMAT(DAY([Date]),"00"))
 
Level2 =
UNION(DISTINCT(SELECTCOLUMNS(DimDate,"Value",FORMAT([Day],"00"))),{"Total","Average"})
 
Count Error = CALCULATE(COUNT('Sheet1'[Error Type]))
 
Measure 2 =
VAR _DAY = CALCULATE(COUNT('Sheet1'[Trade Date]),FILTER(DimDate,DimDate[DayKey] = MAX(Level2[Value])))
VAR _SUM = [Count Error]
var Validdays = FILTER(VALUES('Sheet1'[Trade Date]),CALCULATE(COUNT('Sheet1'[Error Type]))>=10)
VAR _AVERAGE = IF(COUNTROWS(Validdays)>0,AVERAGEX(Validdays,[Count Error]),BLANK())
RETURN
SWITCH(MAX(Level2[Value]),"Total", _SUM,"Average", _AVERAGE,_DAY)
 

 

And here attached the screenshots of matrix i get, which is not the same as what i want. Currently as long as one type of error of that day is below 10, it will not be counted in average, like error 2 in Aug. What i want is to show 5.5 for error 2 in AUG average and 4.5 for error 3 in AUG.

 

 Can you guys help me with it? Thank you ver much!

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi LeoLi ，

     

    Based on your request, 'I would like to calculate the average errors each day in a month without counting those days where the total error numbers are below 10,' since August 29 and August 30 had error counts of 2 and 3, respectively, they are not included in the average calculation. Therefore, it is not possible to achieve the expected results of 5.5 and 4.5. If you wish to obtain 5.5 and 4.5 as results, you will need to forgo the condition of 'calculating average errors each day in a month without counting those days that total error numbers is below 10.'.

     

    The Measer 2 should be:

    Measure 2 = 
VAR _DAY = CALCULATE(COUNT('Sheet1'[Trade Date]),FILTER(DimDate,DimDate[DayKey] = MAX(Level2[Value])))
VAR _SUM = [Count Error]
var Validdays = FILTER(VALUES('Sheet1'[Trade Date]),CALCULATE(COUNT('Sheet1'[Error Type]))>=0)
VAR _AVERAGE = AVERAGEX(Validdays,[Count Error])
RETURN
SWITCH(MAX(Level2[Value]),"Total", _SUM,"Average", _AVERAGE,_DAY)

     

     

    Best Regards,

    Bof

    • LeoLi's avatar
      LeoLi
      Frequent Visitor
      1 year ago

      Hello Bof, 

      Thank you for spending time reviewing my question. Sorry for any misunderstanding but what i would like to say about the request description is that i don't want to include thtose days with total error numbers are below 10, which means 213 for Aug 29 and 152 for Aug 30. So that both Aug 29 and 30 should be included in average calculation, while Aug 31 should not since the total error number for that day is 1.

       

      Hope explanations above could make more sense.

       

      Thank you.

  • LeoLi's avatar
    LeoLi
    Frequent Visitor
    1 year ago

    Can anyone help how to count the monthly average, ignoring all the dates which row total is small then 10?