Count unique values in a table

[#1]

Hi all,

I have a problem:

I have a column with vehicle no's and a column with time not available.

Vehicle 1 is 2 times in the table. Now I want to count this vehicle 1 time instead of 2 times, so it's not

a simple count I guess...? I want to know how many unique vehicles there are in the table.

IN this example there 7 rows but 4 unique vehicles.

I want to calculate the average time not available, for vehicle 1 this is 2+4 hours = 6 = 3 hours average.

What kind of formula can I use for this?

please assist!

Vehicle	Hours not available
1	2
2	4
3	4
1	4
2	4
3	3
4	3

[Anonymous]

Avg([Hours not available])

[#3]

ANd how can I count the vehicles then? I want to know that there are 4 unique vehicles in this table.

So

in this table are 4 unique vehicles, vehicle 1 avg time not available is 3 hours, etc.

[#4]

=avg(DISTINCT Hoursnotavailable) as the expression and vehicle as the dimension in a bar chart will give you what you are looking for. See attached.

[giakoum]

create a straight table chart with vehicle as dimension and expressions :

- Avg([Hours not available]) as jeffrey suggested

- count(vehicle) to count how many times it was not available

As it is a chart, every vehicle will show up once

[Anonymous · Accepted solution]

Count(DISTINCT Vehicle)

Although, I'm not entirely clear on what you're trying to accomplish.  If you want the average time for each vehicle, then you would create a "Straight Table" chart with Vehicle as the dimension and Avg([Hours not available]) as the expression.  If you want to know how many unique vehicles you have then either a Text Box or a "Straight Table" chart with no dimension and the expression Count(DISTINCT Vehicle).  I guess that you could put them both in the same chart and keep expression totals on.  That way the totals at the top would show the total average and the total count of unique vehicles.  Each row of the chart would show the average time and 1 for unique vehicles.

[Clever_Anjos]

Count(distinct Vehicle) should return what you need