Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculation average rate with 0 values

Hi everybody,

 

I need your help.

 

Our customer needs to calculate an average rate of efficiency on 6 days, without lines = 0.

 

Example : 

 

DateNotes
05/10/20198
06/10/20195
07/10/20190
08/10/201915
09/10/20190
10/10/201913

 

Average = 6.833...

Expect result = 10.166...

 

Here is the DAX measure we tried to do, but of course, it's not OK.

 

Gamme théorique/charge (6jours glissant) % = Calculate(DIVIDE(sum('Heures théoriques gammes'[Temps théorique ACOUS ELYT]);sum('Heures disponibles centre charge'[Heures disponibles (comprend les effectifs réels)]);DATESINPERIOD('Date période'[Date];max('Date période'[Date]);-6;DAY))

 

Thanks for your help.

 

Karim

 

average
CALCULATE
  • Anonymous's avatar
    Anonymous
    6 years ago
    [6LastNon0Entries Avg] =
var __non0EntriesCount = 6
var __currentDay = max( Calendar[Date] )
var __daysWithNonZeros =
	filter(
		all( 'Calendar'[Date] ),
		'Calendar'[Date] <= __currentDay
		&& [Total Hours] > 0
	)
var __6DaysWithNonZeros =
	topn(__non0EntriesCount,
		__daysWithNonZeros,
		'Calendar'[Date],
		desc
	)
var __sum =
	calculate(
		[Total Hours],
		__6DaysWithNonZeros
	)
var __avg =
	divide( __sum, __non0EntriesCount )
var __6DaysWithNonZerosCount =
	countrows( __6DaysWithNonZeros )
var __shouldCalc =
	__6DaysWithNonZerosCount = __non0EntriesCount
return
	if( __shouldCalc, __avg )

     

    Best

    D

12 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Anonymous 

    Please, give your data example.

    I dont see any hint about any field in your measure.

    What is Notes?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi az38 

     

    Thanks for your answer.

     

    You'll find below client data examples.

     

    DateTemps théoriqueHeures disponibles (comprend les effectifs réels)Gamme théorique/charge (6jours glissant) %
    05/10/2019 00:00  55.71
    06/10/2019 00:00  46.73
    07/10/2019 00:0098.98653099999994195.99999852.71
    08/10/2019 00:00113.21901100000008195.99999856.06
    09/10/2019 00:00121.79969699999995195.99999858.52
    10/10/2019 00:0086.29423900000013195.99999852.37

     

    But Gamme théorique/charge (6jours glissant) % is not OK.

    The client needs to calculate on "6 days before"...

     

    Hope the need is clear 🙂

     

    Thanks. 

    • az38's avatar
      az38
      Community Champion
      6 years ago

      Anonymous whats the expected values for your example?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        az38 

         

        When you calculate the average of the last column, you should have 36,61, instead of 53,683.

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    When you calculate an average from a sequence of numbers and want to ignore 0's, you have to just sum up all the numbers (0's don't contribute anything) as they are and find out how many non-0's you have in the sequence, then divide by the count. Quickly, a measure that would give you such an average going 5 days back (as you want to have the last 6 days counting the one you're on right now) would be:

    // Calendar must be the Date table in your model.
// Slicing can only be done through dimensions.
// All columns in your fact table must be hidden.

// Base measure
[Total Hours] = SUM( FactTable[Hours] )

[6-Day Avg] =
var __totalDays = 6
var __currentDay = lastdate( Calendar[Date] )
var __firstDay =
	dateadd( __currentDay, -(__totalDays - 1), day)
var __periodToAvgOver =
	datesbetween(
		Calendar[Date],
		__firstDay,
		__currentDay
	)
var __sum =
	calculate(
		[Total Hours],
		__periodToAvgOver
	)
var __countOfNonZeroDays =
	sumx(
		__periodToAvgOver,
		1 * ( [Total Hours] > 0 )
	)
var __avg =
	divide( __sum, __countOfNonZeroDays )
return
	__avg

    Take the above, think it over and adjust to the problem at hand.

     

    Best

    D

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello,

       

      az38 you're right, my result is false. For every record, I need to do Temps Théorique / Heures disponibles. Then I want to make an average of it on last 6 days, without 0 values. So in this example, I should divide by 4 records. Hope one more time I'm clear 🙂

       

      Anonymous Thanks for your answer ! Your code seems to be good, I'll validate it with the client, and come back to you ASAP.

       

      Thank you very much guys !

       

      Karim

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous,

         

        I come back to you, because I did a mistake in my explanation of the problem.

         

        You will find below an example :

         

        DateABA/B (%)Avg -6 OKAvg -6 NOT OK
        04/10/201910020050  
        05/10/2019     
        06/10/2019     
        07/10/201912020060  
        08/10/201914020070  
        09/10/201916020080  
        10/10/201918020090  
        11/10/20191502007570,8375
        12/10/2019     

         

        In fact, the client needs to know the avg of the 6 last records <> 0.

        On the 11th of october, if I do -6, I should stop on 6th, but as it's empty, I take the 5th, which is empty, so I need to take the 4th, which is OK.

         

        Is it possible to handle it ? Because in fact, all the date functions work on calendar date, without handling if the line corresponds to the wanted filters.

         

        Hope it's clear for you 🙂

         

        Thanks for your help.

         

        Karim