Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Show total average in a card

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
babyjb123_1-1665780992638.png

 

 

 

Employee NameJob TitleHours 84 Hours Rule Inspection CountsCompletion
Allan DooleForeman186300%
Andrew LogueForeman8011100%
Billy FleseForeman235.534100%
Bradley LandryForeman  00%
Bradley NorrisForeman104200%
Brandon JohnstonForeman16322100%
Cameron BrittonForeman2403133%
Carl HacheForeman104200%
Colin WoodForeman16025100%
Dale HankinsForeman2584375%
Darryl HuntForeman98200%
David ThompsonForeman26100%
Derek GasiorForeman118200%
Donald WeberForeman6711100%
Dylan GrantForeman152.522100%
Dylan KeatsForeman  00%
Earl O'RourkeForeman108.5200%
Evan DeGiobbiForeman  0 
Gordon GillisForeman93200%
Gordon NayanookeesicForeman153200%
James AdamsForeman166200%
James SelbyForeman7611100%
James W. RankinForeman24734100%
Jason BeaulieuForeman  0 
Jeffery BraceForeman16322100%
Joey HardingForeman169300%
Jonathan CunninghamForeman1803133%
Jonathan MacDonaldForeman45100%
Joseph NabessForeman15822100%
Julian T.J. SobkowForeman25236100%
Justin GauthierForeman96200%
Keith LewisForeman  0 
Mark ParsonsForeman107200%
Mathieu SavoieForeman9122100%
Matthew Bil 
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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]))

vpollymsft_0-1665976369239.png

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.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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]))

vpollymsft_0-1665976369239.png

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
Memorable Member
Memorable Member

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
Not applicable

It doesn't work.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.