Help with counting

[bryan_d]

Hi there

I can't seem to get this to work - hoping you can help!

I have the following columns in a large table extracted from print logs:

DateGenerated     Time     Hour     Environment

1.2.2016               10:14     10         LEGACY

1.2.2016               11:15     11          NEW

The columns have much more information, but essentially I just want to count the number of times "LEGACY" & "NEW" appear on a certain date. Ideally, I would also be able to count date & hour - but I don't want to be greedy!

This is all to produce a % of total for each day.

Hope you can help! Thanks

[SabineOussi]

Hello everyone,

I'm a bit confused here.

Doesn't the below calculated measure give the same results regardless of how many environment types you have? That way if you have more than two environments, the percentage will be calculated using only one query rather than one or two for each type.

Environment % =
DIVIDE(COUNTROWS(Table) ,

CALCULATE(
COUNTROWS(Table),
ALL(Table[Environment])
))

You then create a matrix where you put your date as rows, environment as columns, environment summarized as count and the calculated environment % as values.

Does it also work?

[Sean · Accepted solution]

@bryan_d 

Here's the example I created

The result looks like this

Here are the Measures I used

LEGACY # = COUNTROWS(FILTER(Table1, Table1[Environment]="LEGACY"))
LEGACY % = DIVIDE([LEGACY #], [TOTAL #], 0)
NEW # = COUNTROWS(FILTER(Table1, Table1[Environment]="NEW"))
NEW % = DIVIDE([NEW #],[TOTAL #],0)
TOTAL # = [LEGACY #] + [NEW #]

Hope this helps!

[bryan_d]

That's great Sean - thanks so much!

Quick question - just wondering if you manually created the entries in the 'DateGenerated' & 'Hour' columns, or were these automatically populated?

Thanks

Bryan

[Sean]

@bryan_d DateGenerated and Time yes manually I just created a small table to show you the result - Hour derived from time

Anyway the problem with the above formula suggested although it works in a Matrix - it has more limited use

How are you going to display just the Legacy # with a Card? (not multi-row card)

Can you create a Line and Clusted Column Chart?

[MattAllington]

Sorry, yes distinct count only works if you have a unique row identifer.  Your countrows formula are the best for your data.

[Anonymous]

Hi @bryan_d,

   you can use the COUNTA() function.

I'm sorry @MattAllington I tried with the DISTINCTCOUNT() function, but I simply got 2 referred to the two different environments (as you can see from the screenshot).

Let Me Know If It Works.

#I'M Not An Expert#

[MattAllington]

Put dateGenerated in rows in a matrix (or on a chart axis or what ever).  Then write 

=DISTINCTCOUNT(yourTableName[Environment])

same applies for the others