Forum Discussion

bryan_d's avatar
bryan_d
Regular Visitor
10 years ago
Solved

Help with counting

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

Need Help
  • Sean's avatar
    Sean
    10 years ago

    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!

7 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion
    10 years ago

    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 

  • Sean's avatar
    Sean
    Community Champion
    10 years ago

    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!

    • MattAllington's avatar
      MattAllington
      Community Champion
      10 years ago

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

    • bryan_d's avatar
      bryan_d
      Regular Visitor
      10 years ago

      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's avatar
        Sean
        Community Champion
        10 years ago

        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?

  • SabineOussi's avatar
    SabineOussi
    Skilled Sharer
    10 years ago

    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?