CODEWITHSUNDEEP
daxpowerbi
Hellfire
Hellfire

Reputation: 21

Count number of occurrences in a column

I ran a survey of members of different groups (e.g. Group 1 to 4) about whether they agree or disagree with something. I'm attempting to chart these responses in Microsoft PowerBI Desktop.

I have a data model loaded that includes columns like these in a single table:

Group    Question1   Question2
Group1   Agree       Agree
Group1   Disagree    Agree
Group4   Disagree    Disagree
Group3   Agree       Agree
Group2   Disagree    Agree
Group2   Agree       Disagree
Group4   Agree       Agree
Group1   Agree       Disagree

I'd like to know if there is a DAX that can count the number of occurrences of the words "Agree" and "Disagree" such that I can have those as values on a stacked bar chart (one chart per question):

Group1   Agree---------   Disagree----
Group2   Agree-------  Disagree-------
Group3   Agree----------  Disagree----
Group4   Agree------  Disagree--------

(apologies for the ASCII-art)

I've tried using the COUNTA() function but it just keeps returning the number of rows. I've also tried just dragging the question column in as a data field but it again, just makes each bar the same length as they all have the same number of total responses.

Thanks in advance.

Upvotes: 2

Views: 16392

Answers (3)

alejandro zuleta
alejandro zuleta

Reputation: 14108

You can create a table per Question with the following DAX expression:

Table = SUMMARIZE('YourTable',YourTable[Group],"Agree",
COUNTAX(FILTER('YourTable',YourTable[Question1]="Agree"),
YuorTable[Question1]),"Disagree",
COUNTAX(FILTER('YourTable',YourTable[Question1]="Disagree"),YourTable[Question1]))

Example:

YourTable

enter image description here

For Question1 you will get the following table:

enter image description here

Once you have the table just create the chart you need.

Let me know if this was helpful.

Upvotes: 0

Vasim
Vasim

Reputation: 3143

We would need to create six measures (for clear and simplification)

1) Agree Q1 = CALCULATE(COUNTA(SO_table[Question1]),SO_table[Question1]="Agree")

2) Agree Q2 = CALCULATE(COUNTA(SO_table[Question2]),SO_table[Question1]="Agree")

3) Disagree Q1 = CALCULATE(COUNTA(SO_table[Question1]),SO_table[Question1]="Disagree")

4) Disagree Q2 = CALCULATE(COUNTA(SO_table[Question2]),SO_table[Question1]="Disagree")

5) Total Agree = Agree Q1 + Agree Q2

6) Total Disagree = Disagree Q1 + Disagree Q2

You can then use Stacked bar and plot Total Agree & Total Disagree

Upvotes: 0

greggyb
greggyb

Reputation: 3798

You'll need to unpivot your table - to have a structure like follows:

Group  |  Question  |  Answer
Group1 |  Question1 |  Agree
Group1 |  Question2 |  Disagree
...

Your measures then look like the following:

RowCount:= COUNTROWS(FactAnswer)

AgreeCount:=
CALCULATE(
    [RowCount]
    ,FactAnswer[Answer] = "Agree"
)

DisagreeCount:=
CALCULATE(
    [RowCount]
    ,FactAnswer[Answer] = "Disagree"
)

Upvotes: 3

Related Questions