Count of text column in a table visual

[RobertSlattery]

In the table view I automaticaly get a column sum in the total row at the bottom of the table.  In Excel, I can add a totals row to a table and select count to get the number of non-blank entries in a column.  How do I do this in the Table visual in Power BI Desktop?

[v-shex-msft]

HI @RobertSlattery,

You can also try to use below measure to filter the total row and modify the calculated result:

TotalCount = if(COUNTROWS(Table)=COUNTROWS(ALL(Table)),SUM(Table[Column]),COUNT(Table[Column]))

Regards,

Xiaoxin Sheng

[ImkeF]

So you want to show the values as a SUM but only at the Total (and subtotals) they should be COUNT?

Then you have to create a measure like this:

TotalCount= if(ISFILTERED(<YourColumn>),SUM(<YourColumn>), COUNT(<YourColumn>))

[RobertSlattery]

First of all, thanks for your answer,

Sorry, I am completely new to Power BI...

I looked up measures and it seems like this is a DAX formula in a calculated column that operates on the whole table. Is that right?

It's doing the exact same thing as selecting a summarizing function for a field in the values section, except that its a new column.

I prefer not to use DAX for stuff that I can do in M but as far as I can tell, it doesn't really solve my problem in this case?

I just want to manage the totals row that appears automatically at the bottom of a table.

When I connect data to the table, it does a nice job of automatically adding a Totals section at the bottom. My question is, can I control the aggregation functions in the Totals row at the bottom of the table like I can in an excel table?

[TomMartens]

Hey,

currently you can't control (manage) the behavior (aggregation function) for measures in "total" rows.

Changing the aggregation type of a measure in a visual, let's say a table, affects the complete visual and not just the total rows.

I see your point, that this could be a useful feature. Whereas the proposed solution affects the complete model. For this reason I understand, if your trying to avoid DAX and prefer M to avoid clutter in your model.

But unfortunately this would not help here.

Maybe you should consider to propose this as a new feature on ideas.powerbi.com 

[ImkeF]

Thanks @TomMartens for the clarification.

I would like to add that my suggestion is meant as a measure and NOT as a calculated column in DAX.

You would use that INSTEAD of the field/column you've already dragged into. So it wouldn't become a new column as you've said, but instead replace the existing column with a logic that is different for certain types of rows: The rows who have a filter attached to them would be summed and only those rows who have no filter (which is only the total) would return a count.

I would also support a feature for that, escpecially if it includes sub-totals as well. Please post link here - thanks.

[RobertSlattery]

OK, first of all, thanks to @TomMartens, that's exactly what I'm talking about.

Second, @ImkeF, OK, sounds like maybe what I need but, can you explain where I enter the code?

[ImkeF]

In table view click the elipse near the table name -> choose: New Measure -> formula bar will pop up:

For beginners I can strongly recommend this video: https://www.youtube.com/watch?v=cfogi9vGQLc&list=PL1N57mwBHtN2NhxTvyO6TquHuZOMh4BMq&index=2&t=1581s

[RobertSlattery]

Sorry for the delayed response: competing priorities and thanks for the link, luckily I speak fluent Australian!

Based on that (and your other reply) I now understand what a measure is and how it is different from a calculated column.

Unfortunately my question was too unclear, I want to count text values so the sum function in your suggestion returns an error.

But, anyway, I don't understand what the filter function is doing and how it would be different for different rows since the function tests if the whole column is filtered.

What strategy are you suggesting exactly? Are you trying to use the filter to operate on the totals row? That doesn't make sense to me because I would have thought that the Totals row is like an artefact created by the visualisation and is not addressable as part of the table, no? … sorry, I'm a bit unclear.

I assume that the SUM function is a means of getting the value of the column in current row and, of course that doesn't work for a text value so, I tried to find another way to reference the value of a field in the current record but, this doesn't work…

TotalCount= if(ISFILTERED([Column Name]),EARLIEST([Column Name]), COUNT([Column Name]))

I get the error…

EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

[ImkeF]

Hi Robert,

unfortunately I'm still not clear about your needs.

Would it be possible for your to post a picture or example of your table (do you want to count rows, non-empty values within a column...): how shall your results look like?

[RobertSlattery]

Sorry, and thanks for persisting, please see below...

The first column is text values and the second is random integers.

In this case I chose the default (Sum) for the aggregation of the number column and this just sums each entry (Sum(x) = x).

Where it says Total in the Totals row at the bottom, I want it to have the count of the number of entries in the first column (either distinct or absolute values, I can just chose the appropriate function) instead.

It seems I can't do this.

[ImkeF]

You can do it like this:

With this measure: TotalCount = if(ISFILTERED(Table1[Column1]),VALUES(Table1[Column1]), COUNT(Table1[Column1]))

But in order to do so, you need at least one column in your table that returns single rows/lines.

If you just drag measures into your table, they will be aggregated to just one row.

Just play around with it: https://1drv.ms/u/s!Av_aAl3fXRbehasLDyC8HvSawY_b-A

[RobertSlattery]

Thanks very much for the example, unfortunately it doesn't give what I'm looking for because it doesn't act like a total.  If I filter the text column for example, it no longer shows a count, If the filter returns one row, the value in that row is shown in the total, if the filter leaves more than one row, I get an error.

Seems like I need to add a feature request as suggested.

Regarding your formula, I am still quite confused about the strategy behind your approach.  I don't understand what the ISFILTERED function is there for.  Oh well, maybe it will be clear later.  Thanks anyway.