SUM based on Text Criteria

Question

I have a STRING column called Question, this question column provides two possible answeres, either 'Yes; or 'No'

How do I create formulas to sum the amount of times a Yes shows up, the amount of times a No shows up and the amount of times that both of them show up?

Hamza Jap-Tjong · Accepted Answer

Hey ErickRuiz7

You could use a measure filter to achieve this. Let's assume we have 2 columns; question and answer. A formula would look like this:

(Count([Question]), [Answer])

Now you can click on answer and it will show the option Filter. Click on that and you can set the desired filter. This will make the aggregation (in this case a count) count the number of questions for which the answer is , for example, Yes.

This can be used in more advanced scenario's. In more traditional scenario's you could add Answer as a widget filter. Or add it to the row/column (pivot table) or for example the break-by in a chart. This will automatically list all found values and counts by it

Hamza

david-h · Answer

Hi Erick, not sure I understand exactly the use case you are on, but it seems that you could use multi pass agregation (kind of adding a group by in an intermediate calculation in your measure). You may also use this old IT trick : yes=1 and no=0. Let us know if this can be useful in your use case, and if not, maybe you can share a siplified data model.

Best,

David.