Forum Discussion

Datastud3nt's avatar
Datastud3nt
New Member
3 years ago

Appended Table Summarised Results - Count a distinct value by ROW

Hi everyone,

 

I've found multiple topics about counting specific values but based on columns - I need that summarised by row in 'new columns'

 

In excel, the formula would be a simple  = COUNTIFS(ROW RANGE ,"VALUE")

 

Example of a table below (the results I need as an output is highlighted in RED)

 

Thank  you all x

 

 

 

 

 Question 1Question 2Question 3Question 4Question 5Question 6Question 7Question 8Strongly AgreeAgreeDisagreeStrongly Disagree
Row 1Strongly AgreeAgreeDisagreeStrongly DisagreeStrongly AgreeAgreeDisagreeStrongly Disagree2222
Row 2Strongly AgreeAgreeDisagreeStrongly DisagreeStrongly AgreeAgreeDisagreeStrongly Disagree2222
Row 3Strongly AgreeAgreeDisagreeStrongly DisagreeStrongly AgreeAgreeDisagreeStrongly Disagree2222
Need Help

3 Replies

  • iceparrot's avatar
    iceparrot
    Advocate II
    3 years ago

    Hey Datastud3nt , 
    you can solve your problem by creating a new measure for each of the summary colums and use the following DAX code as example:

     

    COUNT_STRONGLY_AGREE = 
var q1 = CALCULATE(COUNT(ANSWERS[Question1]), ANSWERS[Question1]="Strongly Agree")
var q2 = CALCULATE(COUNT(ANSWERS[Question2]), ANSWERS[Question2]="Strongly Agree")
var q3 = CALCULATE(COUNT(ANSWERS[Question3]), ANSWERS[Question3]="Strongly Agree")
var q4 = CALCULATE(COUNT(ANSWERS[Question4]), ANSWERS[Question4]="Strongly Agree")
var q5 = CALCULATE(COUNT(ANSWERS[Question5]), ANSWERS[Question5]="Strongly Agree")
var q6 = CALCULATE(COUNT(ANSWERS[Question6]), ANSWERS[Question6]="Strongly Agree")
var q7 = CALCULATE(COUNT(ANSWERS[Question7]), ANSWERS[Question7]="Strongly Agree")
var q8 = CALCULATE(COUNT(ANSWERS[Question8]), ANSWERS[Question8]="Strongly Agree")

return q1+q2+q3+q4+q5+q6+q7+q8

     

     

    This calculates the sum of Strongly Agree as answers. Add it as extra colum in your table visual and you'll see the result.


    If this suggestions helps you please mark it as solution.

     

    • Datastud3nt's avatar
      Datastud3nt
      New Member
      3 years ago

      Hi iceparrot thanks for your reply, however my dataset is an employee survey with 60 columns- that's why I wanted to create 4 different measures or columns and display the counts BY ROW.

       

      The results of those 4 new columns will then feed another piece of analysis that will provide 'SENTIMENT' of the survey (based on the counts of agree/disagree/etc) -each employee will get a 'Sentiment Result' with values like positive, negative, slightly negative, sliglty positive or 50/50).

       

      If that was done in excel the formula would be as simple as : COUNTIFS(ROW RANGE ,"VALUE")

  • iceparrot's avatar
    iceparrot
    Advocate II
    3 years ago

    Hi  Datastud3nt , 

    then here's a different solution for your problem. 

    Open the Power Query Editor by clicking the Transform Data button in the upper tab menu:

     

    If you then select your table you should see something similar like this:

     

    Duplicate the existing answer table by right clicking on the table in the left Queries pane:

     

     

    Then select the newly created table. I named it ANSWERS_PIVOT. Select all colums with answers except your Row column which is here represented by the Answer column. 

    Then do a right click and select Unpivot Columns.

     

    The result should look somethink like this

     

    Click Save and switch back to your report view. 
    There you create a measure for each answer like this: 

    COUNT_STRONGLY_AGREE = CALCULATE(COUNTX('ANSWERS_PIVOT', 'ANSWERS_PIVOT'[Value]), 'ANSWERS_PIVOT'[Value] = "Strongly Agree")

     

    Add the measures as colums to your ANSWERS table and you will get the following result: