9 Replies Latest reply on Jun 29, 2018 8:00 AM by Zhouyi Zhang

# Dividing sum by filtered distinct count

Hi everybody,

I am fairly new to Tableau and cannot figure out how divide the sum of my paid amount by the distinct count of the episode. For example, each episode has multiple fields. Every episode has a trigger, but not everyone has PTOT or Home Health I only want to divide by the distinct count of the trigger. Right now when i do Sum(Allowed Amount)/Countd(Episode id) it divides by however many there are in each category. I know this is probably hard to follow so I have added a packaged workbook.

Thanks,

Gino

• ###### 1. Re: Dividing sum by filtered distinct count

Hi

you are right - difficult to follow

see the attached

is this what you expected

see the attached

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Dividing sum by filtered distinct count

I don't quite follow what you're trying to do, but it sounds like you are have a level-of-detail problem. If you need to do a count distinct outside of the level of detail you are currently on (e.g. it's looking at the specific category), then using a level-of-detail calculation should give you what you need. For more on LODs, see Overview: Level of Detail Expressions

For example, if you wanted to divide your allowed amount by the distinct count of episode IDs, without considering the level of detail (so basically, count everything in your data set), you could do the following:

Sum([Allowed Amount])/{FIXED : Countd([Episode id])}

If you wanted this LOD to account for the filters you have in place, then you'd need to right-click on each filter and select "Add to Context". This will ensure that the filter is applied before the LOD is evaluated.

• ###### 3. Re: Dividing sum by filtered distinct count

Hi Jim,

Thanks for the quick reply. I need the sum of all of the "swimlane subcategories" to be divided by the distinct count of the trigger. So in the box that you it would be divided by 75.

• ###### 4. Re: Dividing sum by filtered distinct count

Hi, Gino

I hope I understand your question, please find below screenshot of result. If this is something you are after, please find attached workbook.

Hope this helps

ZZ

• ###### 5. Re: Dividing sum by filtered distinct count

That can be done by change the count formula to this

returns this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 6. Re: Dividing sum by filtered distinct count

ZZ,

This is definitely on the right track, but when I filter the results it does not divide by the new trigger count. Is there any way that you can get it to do that?

Thanks

Gino

• ###### 7. Re: Dividing sum by filtered distinct count

Hi, Gino

I will suggest to add the filter into context if I understand your question correctly.

Have a try to add all your filters into context and let me know if it doesn't work.

ZZ

• ###### 8. Re: Dividing sum by filtered distinct count

That worked. THanks a lot for your help!!

• ###### 9. Re: Dividing sum by filtered distinct count

No worries, I am glad to help

ZZ