topic Re: Calculation average rate with 0 values in DAX Commands and Tips

Calculation average rate with 0 values

Anonymous — Tue, 10 Mar 2020 14:47:20 GMT

Hi everybody,

I need your help.

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

Example :

Date	Notes
05/10/2019	8
06/10/2019	5
07/10/2019	0
08/10/2019	15
09/10/2019	0
10/10/2019	13

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

Re: Calculation average rate with 0 values

az38 — Tue, 10 Mar 2020 19:23:49 GMT

Hi Anonymous

Please, give your data example.

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

What is Notes?

Re: Calculation average rate with 0 values

Anonymous — Wed, 11 Mar 2020 08:25:13 GMT

Hi az38

Thanks for your answer.

You'll find below client data examples.

Date	Temps théorique	Heures 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:00	98.98653099999994	195.999998	52.71
08/10/2019 00:00	113.21901100000008	195.999998	56.06
09/10/2019 00:00	121.79969699999995	195.999998	58.52
10/10/2019 00:00	86.29423900000013	195.999998	52.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.

Re: Calculation average rate with 0 values

az38 — Wed, 11 Mar 2020 08:34:34 GMT

Anonymous whats the expected values for your example?

Re: Calculation average rate with 0 values

Anonymous — Wed, 11 Mar 2020 09:25:14 GMT

az38

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

Re: Calculation average rate with 0 values

az38 — Wed, 11 Mar 2020 10:42:46 GMT

Sorry, Anonymous

I can not understand how do you get 36% from this data 🤔

Re: Calculation average rate with 0 values

Anonymous — Wed, 11 Mar 2020 14:31:02 GMT

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

Re: Calculation average rate with 0 values

Anonymous — Thu, 12 Mar 2020 08:06:51 GMT

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

Re: Calculation average rate with 0 values

Anonymous — Fri, 13 Mar 2020 14:47:24 GMT

Hi Anonymous,

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

You will find below an example :

Date	A	B	A/B (%)	Avg -6 OK	Avg -6 NOT OK
04/10/2019	100	200	50
05/10/2019
06/10/2019
07/10/2019	120	200	60
08/10/2019	140	200	70
09/10/2019	160	200	80
10/10/2019	180	200	90
11/10/2019	150	200	75	70,83	75
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

Re: Calculation average rate with 0 values

Anonymous — Fri, 13 Mar 2020 15:29:03 GMT

[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

Re: Calculation average rate with 0 values

Anonymous — Fri, 13 Mar 2020 15:54:09 GMT

Here's a file with the working measure.

https://1drv.ms/u/s!ApyQEauTSLtOgYMnsGk__jeAJkvMEA?e=5gKPk2

Best
D

Re: Calculation average rate with 0 values

Anonymous — Fri, 13 Mar 2020 20:06:01 GMT

Anonymous thank you very much !

it seems to be the expected result. I'll validate it with the client, and come back to you ASAP.

Re: Calculation average rate with 0 values

Anonymous — Fri, 13 Mar 2020 21:24:34 GMT

Don't forget to give me Kudos 🙂

Best
D