Averaging the number of IDs when having duplicate records

[AutoJL]

Hi everyone!

Let me introduce my data set before explaining the issue since I believe this order will make it easier to explain and understand.

 - My data records are IDs that are read by barcodes on a system.

 - Any ID can be introduced ( and therefore read) into the system various times. This means we can have 2 records of the same ID.

 - An ID can be failed to read and this outputs the "Barcode Error" in the ID field.

This is how it looks in PowerBI:

 

With this data I can now know how many barcode reader erros I have, the number of total records, the number of unique IDs, etc.

My issue comes when trying to obtain the average number of times that records are input in the system. For some reason no matter what I try I cant get the correct amount. What I have tried:

 - Using my "number of inputs column" ( this is calculated ill post the formula below too for inspection) and simply selecting the "show average" in the card visualization:

-Using measures and DAX ( I have no PowerBI training and as such this are all self taught and learned by trial and error)

Attempt 1:

Attempt 2:

I suspect I am not being able to state correctly that I want unique IDs only for that calculation and thus PowerBI is actually using all the records for the calculation ( for example ID 0161622432 is repeated 5 times so it is adding the value "5" Five times in the average calculation). This is just my suspicion thought I dont have the DAX and/or PowerBi knowledge to actually tell.

I know all this calculations are off because I exported the data list to excel, filetered for unique values, removed the "Nobarcode" record and the correct average is 1,40092521.

This is the formula for my custom colum in case it is of use or is related to the problem

Number of inputs = CALCULATE(COUNT([SampleID]),FILTER(Samples,[SampleID]=EARLIER([SampleID])))

Can someone tell me where my error is ( so I dont fall again and leanr the proper way) and help me out to get the correct measure?

Many thanks in advance

[v-yueyunzh-msft · Accepted solution]

Hi , @AutoJL 

According to your description, the issue is like your thought:

The calculation logic for your dax is above.And this is my test data.My table name is 'Table' in Desktop.

If you need to get the right average, you can try to use this dax code:

Right = AVERAGEX( FILTER( SUMMARIZE('Table','Table'[SampleID],'Table'[Number of inputs]) , [SampleID] <> "NO_BARCODE") , [Number of inputs])

Then we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[AutoJL]

Many thanks for the help @v-yueyunzh-msft ! your solution worked perfectly. I will study more about the Summarize function.

Many thanks for the help!

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523