Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Average Distincts per TimeStamp

Hello all.

 

I'm quite new with Power BI and I'm facing a problem while I try to create a measure.

 

I have a table with events, its time stamps and the machine with the problem identified with a serial number.

 

I wish to know the average of distinct SN per day in a time frame. I know that it is easy to do with a chart, but I need the value as a measure for next calculations.

 

I show you an example below:

 

 

Here are my data. And I want to obtain:

 

 

In Excel, the path I follow is the next:

 

SUM(  COUNTIFS ( LISTOFDAYS = DAY && LIST_OF_SN = SN1 ) + COUNTIFS ( LISTOFDAYS = DAY && LIST_OF_SN = SN2 ) + .... + COUNTIFS ( LISTOFDAYS = DAY && LIST_OF_SN = SNn )

This option isn't good neither in Excel nor, of course, in Power BI.

 

What I've tried?

 

Create a new table with one column where are the days.

Distinct counts of SN from original table.

 

And now, with the help of and slicer I want to use both tables in the same page filtering by SN and by date, but I have issues when I link the tables. I've used a link Many to One form original table (same day due to events in different rows) to the new table (one different day per row). Then, when I use the slicer to select the time frame is all ok, but doesn't apply the SN filter also I have.

 

How can I do that?

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi Anonymous,

     

    According to your description, I have made a sample for your reference.

     

    I assume you have a table called "Table1" like below.

     

     

    Then you should be able to use the formula below to create a measure to calculate the average of distinct SN per day in a time frame.

    AvgCountPerDay = 
AVERAGEX (
    SUMMARIZE (
        Table1,
        Table1[Time],
        "DistinctCountOfSN", DISTINCTCOUNT ( Table1[SN] )
    ),
    [DistinctCountOfSN]
)

     

    Here is the sample pbix file for your reference.:smileyhappy:

     

    Regards

2 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi Anonymous,

     

    According to your description, I have made a sample for your reference.

     

    I assume you have a table called "Table1" like below.

     

     

    Then you should be able to use the formula below to create a measure to calculate the average of distinct SN per day in a time frame.

    AvgCountPerDay = 
AVERAGEX (
    SUMMARIZE (
        Table1,
        Table1[Time],
        "DistinctCountOfSN", DISTINCTCOUNT ( Table1[SN] )
    ),
    [DistinctCountOfSN]
)

     

    Here is the sample pbix file for your reference.:smileyhappy:

     

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Works fine! Many thanks for your time! ;)