8 Replies Latest reply on Apr 30, 2020 4:45 PM by Stephen McClendon

# How can I divide 2 different counts of a dimension?

I need to figure out how to get the closing ration....

This is my cross tab.

• ###### 1. Re: How can I divide 2 different counts of a dimension?

Could you provide a sample packaged workbook? We'll need to understand the structure of your data and how you've built the chart in order to properly help with this question.

• ###### 2. Re: How can I divide 2 different counts of a dimension?

Sure....Here you go.

Again, I am trying to divide the Closed number on a month by the open number in a month.

• ###### 3. Re: How can I divide 2 different counts of a dimension?

Are you planning to display the resulting data in table form as you've shown here?

• ###### 4. Re: How can I divide 2 different counts of a dimension?

Yes. I would like that. Possibly on a third row. But I am okay with any way to display it on the same worksheet.

• ###### 5. Re: How can I divide 2 different counts of a dimension?

Stephen,

To get the desired result, you should do a workaround.

1. Create a calculated field

2. Add the calculated field in new sheet without Phase as below

And create a dashboard and include both the sheets and will exactly look like your desired result.

Attached the workbook for your reference.

I hope this helps If it does, can I ask you to mark it as helpful and correct? this will help other users to find correct answers and will allow us to close the thread. Many thanks!

Thanks,

Anand

• ###### 6. Re: How can I divide 2 different counts of a dimension?

Thank you. Love how you combined the two sheets on a dashboard. That helps me with some other projects as well.

• ###### 7. Re: How can I divide 2 different counts of a dimension?

I'd suggest creating three calculated fields like this:

Open Projects

```// Count of open projects
COUNTD(
IF [Phase]="Open" THEN
[Project Number]
END
)
```

Closed Projects

```// Count of closed projects
COUNTD(
IF [Phase]="Closed" THEN
[Project Number]
END
)
```

Closing Ratios

```// Ratio of Closed to Open
[Closed Projects]/[Open Projects]
```

Then use Measure Names on the rows shelf instead of Phase.

2 of 2 people found this helpful