Show total average in a card

[Anonymous]

Hi all,

I am calculating a column called "Comletion".

Completion =AVERAGEX (VALUES ( 'Staff'[Staff Name]), 'Inspection'[0 or 1] )

If the Inspection Counts > 84 Hour Rule, then the completion will be 100%, otherwise, the completion will be equal to (inspection count/84 Hours Rule). My problem is, there are many blanks of 84 Hours Rule. After I filter out the blank, the Completion showing in the card is 39% which is smaller than 43%. That is because the blank rows are counted as denominator. So I write another measure to filter out the blank hour as this: 

Completion Average = CALCULATE(AVERAGEX(VALUES('Staff'[Staff Name]),'Inspection'[0 or 1]),FILTER('Employee Transaction','Employee Transaction'[Hours ]<>BLANK()))

The resule comes out 31%, which is still not right. 

Could anyone help to write a measure to make the number right in the card?

Thank you in advance!

Branko

Employee Name	Job Title	Hours	84 Hours Rule	Inspection Counts	Completion
Allan Doole	Foreman	186	3	0	0%
Andrew Logue	Foreman	80	1	1	100%
Billy Flese	Foreman	235.5	3	4	100%
Bradley Landry	Foreman			0	0%
Bradley Norris	Foreman	104	2	0	0%
Brandon Johnston	Foreman	163	2	2	100%
Cameron Britton	Foreman	240	3	1	33%
Carl Hache	Foreman	104	2	0	0%
Colin Wood	Foreman	160	2	5	100%
Dale Hankins	Foreman	258	4	3	75%
Darryl Hunt	Foreman	98	2	0	0%
David Thompson	Foreman	26	1	0	0%
Derek Gasior	Foreman	118	2	0	0%
Donald Weber	Foreman	67	1	1	100%
Dylan Grant	Foreman	152.5	2	2	100%
Dylan Keats	Foreman			0	0%
Earl O'Rourke	Foreman	108.5	2	0	0%
Evan DeGiobbi	Foreman			0
Gordon Gillis	Foreman	93	2	0	0%
Gordon Nayanookeesic	Foreman	153	2	0	0%
James Adams	Foreman	166	2	0	0%
James Selby	Foreman	76	1	1	100%
James W. Rankin	Foreman	247	3	4	100%
Jason Beaulieu	Foreman			0
Jeffery Brace	Foreman	163	2	2	100%
Joey Harding	Foreman	169	3	0	0%
Jonathan Cunningham	Foreman	180	3	1	33%
Jonathan MacDonald	Foreman	45	1	0	0%
Joseph Nabess	Foreman	158	2	2	100%
Julian T.J. Sobkow	Foreman	252	3	6	100%
Justin Gauthier	Foreman	96	2	0	0%
Keith Lewis	Foreman			0
Mark Parsons	Foreman	107	2	0	0%
Mathieu Savoie	Foreman	91	2	2	100%
Matthew Bil

[v-rongtiep-msft · Accepted solution]

Hi @Anonymous ,

When the 84 Hours Rule value is blank, the completion  is blank or is 0%? When  the 84 Hours Rule value is 0, can count is inside?

I have created a simple sample, please refer to.

Measure = var _b = SUMMARIZE('Table','Table'[Employee Name],"aaa",[Completionmeasure])
return
IF(HASONEVALUE('Table'[Employee Name]),[Completionmeasure],AVERAGEX(_b,[aaa]))

If I hvae misunderstood your meaning, please provide more details with your desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Shaurya]

Hi @Anonymous,

Use the following the code to create a column first:

Completion = IF('Table'[Inspection Counts]/'Table'[84 Hours Rule ]>1,1,'Table'[Inspection Counts]/'Table'[84 Hours Rule ])

Then create a measure for your card:

Average = CALCULATE(AVERAGE('Table'[Complete]),FILTER('Table', 'Table'[Complete]<1 && 'Table'[84 Hours Rule ]<>BLANK() && 'Table'[Inspection Counts]<>0))

Works for you? Mark this post as a solution if it does!
Consider taking a look at my blog: Forecast Period - Previous Forecasts

[Anonymous]

It doesn't work.