How do I count number of rows for a particular column in a pivot table?

Question & Answer
,
1

I have created a visualization of a pivot table for a QuickSight dashboard.
I have several rows for each column. I would like to get the number of rows for a particular column.
How do I do that?
Thank you.

2

Hi @gopinathpc
what do you mean by “get the number of rows for a particular column”? The row count should be the same for all columns. Do you mean you want to count the rows for id1 and id2 within id column?

BR

1 Like
3

My scenario is this:
col 1 col2
row 1 10
row 2
Screen Shot 2024-04-15 at 9.33.25 PM
6
row 3 5

count 2 1

How do I get the count (last row)?

4

Hi @gopinathpc

you could do a count({col1}) and count({col2})or you could use a pivot table.

BR

5

I am using a pivot table.
Do I need to add 2 calculated fields - count({col1}) and count({col2})?
Thank you.

6

@gopinathpc if you use pivot table already you should change the aggregation of the value.

7

But how do I add this count?

8

I do see count in the values section. But I want both the totals and the count (in separate columns) just for 1 column,
For example if I want these for column, Variance, how do I do this? Looks like I can set only 1 (either sum or total) and not both for this column,

Variance (sum)
Variance (total)

9

Hi @gopinathpc
I guess you would need two columns with variance.
BR

1 Like
10

Thanks ,I created 2 calculated fields and added to the report.
Now, another issue I am facing is that I can’t display the total of Variance (count) in the pivot table.
When I use the format visual and add the column total, I get total for all columns. I want total to be for just 1 column. Can I do this?

11

Hi @gopinathpc
if you want the total for a column you need the “row total”.
column total is horizontal / row total is vertical
BR

12

But I would like to get the column total just for ONE column. But if I use the format
visual I get total for all columns.
How do I get the total just for ONE column only?

13

Hi @gopinathpc
you can create a total just for one column.
Maybe you can use different visuals?
BR

14

Hi @gopinathpc, I am marking this reply as, “Solution,” in case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and please create a new post, so we can look at it from a fresh perspective. (Details on using Arena can be found here - QuickSight Arena)