Forum Discussion

ErickRuiz7's avatar
ErickRuiz7
Cloud Apps
05-28-2024
Solved

SUM based on Text Criteria

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 ti...
dashboards & reporting
  • HamzaJ's avatar
    HamzaJ
    05-29-2024

    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

HamzaJ's avatar
HamzaJ
Data Integration
05-29-2024

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

ErickRuiz7's avatar
ErickRuiz7
Cloud Apps
05-29-2024

Thanks for the suggestion, I tried it and partially work, but I forgot to mention that the question can repeat several times, I have a total of 6 different questions and a total of 950 answers to each question, the approach is conting only 6, do you have any suggestion for me?

  • TriAnthony's avatar
    TriAnthony
    Admin
    06-10-2024

    Hi ErickRuiz7,

    Sounds like you just need to use DUPCOUNT, so the formula should be: (DupCount([Question]), [Answer])

    If this still doesn't provide the result you need, could you send example values of all the relevant columns? A screenshot of the desired result would be really helpful as well. Lastly, could you also elaborate on in what kind of situations both Yes and No could show up as an answer and how that is reflected in the database?