cancel
Showing results for
Did you mean:
Regular Visitor

## 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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft

## Re: Average Distincts per TimeStamp

Hi @Jantgo,

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.

Regards

2 REPLIES 2
Highlighted
Microsoft

## Re: Average Distincts per TimeStamp

Hi @Jantgo,

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.

Regards

Regular Visitor

## Re: Average Distincts per TimeStamp

Works fine! Many thanks for your time! 😉

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors