How to get Total for a colum with data type "text"

[NovaBI]

Hi,

is there an easy way to fix this:

Name

a

b

c

__

Total  3

I.e. text data type in a column, and just print the total also?

I can create a measure and put in a 2nd column, that is clear. But I dont want to have a 2nd column that needs to be manually "changed" to make the table more pretty.

The real case is more complicated, but this very simple one displays the main issue.

[johnt75 · Accepted solution]

Not if that email address is the only unique identifier in the table, no. I've used it previously where I'm either showing the unique ID as a number, or maybe a unique URL linking to a CRM or whatever.

[PaulDBrown · Accepted solution]

Create the measure:

Letter =
IF (
    ISINSCOPE ( 'Table'[Field] ),
    MAX ( 'Table'[Field] ),
    "Total " & COUNTROWS ( 'Table' )
)

add the field and the measure to the visual. In the formatting pane, search for wrap and turn the options off:

Now grab the right boundary for the field column and drag it left:

[NovaBI]

Thanks. Even not too handy (i.e. not out of the box), but by far good enough. Appreciated.

[johnt75]

For this method to work I think you need to have a unique identifier on each row, then you can create a measure like

Name Measure = IF( ISINSCOPE( 'Table'[ID], SELECTEDVALUE('Table'[Name]), COUNTROWS('Table'))

and use the measure in the table visual rather than the column

[NovaBI]

2 questions:

1. What do you mean by Table[ID]? Lets assume i only have one table with one column:

Tablename: Table Columnname: Test

Total = 

Name Measure = IF( ISINSCOPE( 'Table'[ID], SELECTEDVALUE('Table'[Test]), COUNTROWS('Table'))

Not sure what to put in for ID...

2. Error is thrown:

Too many arguments were passed to the ISINSCOPE function. The maximum argument count for the function is 1.

If i am not wrong, we miss one parenthesis also.

[johnt75]

Yes, missing a close parenthesis for the ISINSCOPE.

If you've just the one column then it would be

Name Measure = IF( ISINSCOPE( 'Table'[Test]), SELECTEDVALUE('Table'[Test]), COUNTROWS('Table'))

[NovaBI]

addtional information:

Matrix table,

Rows is a

your measure put into Values

Perhaps i do not fully understand what you meant by "...and use the measure in the table visual rather than the column"

[NovaBI]

Hi @johnt75 ,

thanks for that so far. It is now counting the rows, but i think I still oversee something to make it accurate.

It now looks like:

a     Measure(a Total)

x       x

y       y

z       z

Total 100

If I delete "a" for rows, the total count shows now the Totals, but no entry anymore (makes sense). 

Is it doable to use the Entries from column a (email-adresses e.g.) with the Total of the measure you proposed without showing the entries 2 times?

[johnt75]

You would need some column with unique values in the table, e.g. customer ID. without that it won't work.

[NovaBI]

Hi @johnt75 ,

thanks again for you input. I think i do understand that we need a unique identifier.

I was thinking that unique email-addresses should be okay, as they are distinct and no duplicates are in the data.

Or is it necessary to have a kind of "primary key" that is numeric?

[johnt75]

Its not necessary for it to be numeric, as long as it ties to a single row in your data table it should be fine.

[NovaBI]

That is somehow not the case, even if i cut down the dataset to just 2 user emails, I only see 

Total 2.

if using the measure only

If i put back the email adresses into the rows, it shows

abc@abc.com      abc@abc.com

xyz@xyz.com       xyz@xyz.com

Total                                       2

[johnt75]

Is there another unique column you can add, maybe customer name ?

[NovaBI]

I tried, but the issue persists. I made a test sample to show it:

 

 Measure_Formula: 

Users-Total = IF(ISINSCOPE('Append_regions'[displayName]),

SELECTEDVALUE('Append_regions'[displayName]),COUNTROWS('Append_regions'))

You see the result is still odd.

If i put in the displayName as a Value (table visual) or as a row (in a matrix), the result is still:

 

[johnt75]

Replace the displayName in the table visual with regionID or something, any column which will filter the displayName column down to 1 value

[NovaBI]

Not sure i got that right, but i just put in a regional ID (only one region e.g.), it is:

 

If i change the regionID from one of these user, i got the same picture as before:

For test reasons, i added another user to the region 2:

 

All cases are correct.

I think you/we are on the right track refering the issue, but i don't see the current solution as feasible for a column with distinct values like an email address. 

[johnt75 · Accepted solution]

Not if that email address is the only unique identifier in the table, no. I've used it previously where I'm either showing the unique ID as a number, or maybe a unique URL linking to a CRM or whatever.

[NovaBI]

Okay, thanks for the input! That was a good discussion.

[mh2587]

measure = count(tablename(Name))

[NovaBI]

that is exactly what i do not want 🙂

If i do it like this, sticking the measure to the table results in:

a       1

b       1

c       1

Total 3

Dont want to see the 1 in rows. I know i can get rid of them with the format options, but i am looking for a clean version from the beginning.