10 Replies Latest reply on Apr 8, 2018 6:50 PM by ce.lin

# Subtotals as Percentage of rows with a value

Hello! I have 2 questions I'd like some help with.

1. I have campaign data with 1s and 0s as values to show if a campaign has been finished in the Campaigns Tab . I'd like the subtotals to be a percentage showing the percent of employees who have completed the marketing campaign (e.g. Subtotals for Campaign B for marketing teams A and B would be 5/6=83.3% and 3/4=75% respectively) . What would be the best way to do this? Also, is there a better way of adding the measures to the table? I'm currently using the Max function due to the way I set up the join tables.

2. In the Sales tab, is there a way to show \$0 for periods where an employee doesn't have any sales data using the original measure? Or is the only way to recreate the sales measure as a calculated field with the ZN function?

• ###### 1. Re: Subtotals as Percentage of rows with a value

Hi Ce,

Pl See Screenshot and Attached.

Thanks

Deepak

If it Helps, Pl mark it Helpful and CORRECT to Close Thread.

1 of 1 people found this helpful
• ###### 2. Re: Subtotals as Percentage of rows with a value

That solves the problem with the sales tab, however the campaigns tab is not quite what I'm looking for. I'm looking to have the subtotals on the subtotal line. The current solution is not something I would be able to present to as a solution for a client.

Is there another way you can recommend  to portray which campaigns that have been completed and what that completion percentage is? I'm open to using a graphic a different approach to the sheet completely if that helps. The goal with this sheet is to see how teams are trending and to be able to easily identify employees who haven't completed a campaign. Any filtering mechanism on a dashboard to do this would be great.

• ###### 3. Re: Subtotals as Percentage of rows with a value

here is the Result. I added Name to CONTEXT filter so that you can filter employees.

Thanks

Deepak

• ###### 4. Re: Subtotals as Percentage of rows with a value

Is there a way to show the employees as well, but only have the percentage show on the subtotal lines?

• ###### 5. Re: Subtotals as Percentage of rows with a value

Pl create a view in excel the way u want

• ###### 6. Re: Subtotals as Percentage of rows with a value

This is the updated workbook I have. I'm looking for the following view:

• ###### 7. Re: Subtotals as Percentage of rows with a value

Here it Is:

Thanks

Deepak

1 of 1 people found this helpful
• ###### 8. Re: Subtotals as Percentage of rows with a value

Can you explain what you did? When I try doing it with a new sheet, I get:

• ###### 9. Re: Subtotals as Percentage of rows with a value
• Format SUM(Ratio) to Percentage by Right Click
• go to your Total where it is 500% , Keep Cursor there a menu will appear change from Automatic to Average

Thanks

Deepak

• ###### 10. Re: Subtotals as Percentage of rows with a value

Thanks for the help and explanation Deepak!