Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Datastud3nt
New Member

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
3 REPLIES 3
iceparrot
Advocate II
Advocate II

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:

iceparrot_0-1687266883435.png

 

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

iceparrot_1-1687266949299.png

 

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

iceparrot_4-1687267443814.png

 

 

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.

iceparrot_2-1687267046737.png

 

The result should look somethink like this

iceparrot_3-1687267066877.png

 

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:

iceparrot_5-1687267541451.png

 

iceparrot
Advocate II
Advocate II

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.

iceparrot_0-1687262960066.png


If this suggestions helps you please mark it as solution.

 

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")

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.