9 Replies Latest reply on Jan 13, 2019 8:16 AM by Paul Wachtler

# Calculate the percentage by FIXED

Hi

I'm a newbie

I have an excel file below:

I want to calculate the percentage: 0 <= Hour <= 24 / total hours (similar: 24<Hour<=48 , 48<Hour<=72)

by group [Sub-category] and [Warranty staff]

I created 2 calculated fields:

a = IF [Hour]> 0 and [Hour] <= 24 THEN {FIXED [Sub-category], [Warranty staff]: COUNT ([Hour])} END

b = COUNT ([Hour])

I took a/b but falied

Looking forward to early feedback from everyone!

Thanks

• ###### 1. Re: Calculate the percentage by FIXED

Could you provide a packaged workbook to give us an idea of what you've done so far?

• ###### 2. Re: Calculate the percentage by FIXED

Hi Dinh,

I think for your 'a' calculation you need to include the if statement inside of the count():

{FIXED [Sub-category], [Warranty staff]: COUNT (IF [Hour]> 0 and [Hour] <= 24 THEN [Hour] END)}

Best,

Paul

• ###### 3. Re: Calculate the percentage by FIXED

Hi PAUL!

I tried but I want values of b = 15 (total = 15)

Thanks!

• ###### 4. Re: Calculate the percentage by FIXED

Ken Flerlage a écrit:

Could you provide a packaged workbook to give us an idea of what you've done so far?

Chào Ken!

Tôi rất xin lỗi

Đây rồi!

Thanks

• ###### 5. Re: Calculate the percentage by FIXED

Hi Dinh,

Realized that you wanted to divide by the total count of hours too.  It depends on what else you have on your worksheet.  Count([Hours[) should work if you don't have anything else on the worksheet.  If you do, {fixed : count([Hours])} should work but depends on if you have any filters.  If you have filters, making them context filters will get them to be processed before the fixed LOD calculation.

Paul

1 of 1 people found this helpful
• ###### 6. Re: Calculate the percentage by FIXED

Hi Dinh,

See my last reply. {fixed : count([Hours])} will work for calculating the total count of hours.

Paul

1 of 1 people found this helpful
• ###### 7. Re: Calculate the percentage by FIXED

Hi PAUL!

Thanks so much!

Besides, I created calculation "Group time" to group hours: IF Hour >= 0 and Hour <=24 THEN "24h" ELSE 0 END

Similar "48h" and "72h"

{ FIXED [Moc Gio] : COUNT (IF [Hour]> 0 and [Hour] <= 24 THEN [Hour] END)}

Thanks!

• ###### 8. Re: Calculate the percentage by FIXED

Hi PAUL!

Thanks so much!

Besides, I created calculation "Group time" to group hours: IF Hour >= 0 and Hour <=24 THEN "24h" ELSE 0 END

Similar "48h" and "72h"

{ FIXED [Group time] : COUNT (IF [Hour]> 0 and [Hour] <= 24 THEN [Hour] END)}

Thanks!

• ###### 9. Re: Calculate the percentage by FIXED

You're welcome Dinh!  Glad it worked out for you.  Can you please mark my reply as helpful and correct to close out this thread?  Thanks!