Distinct Count Filter by Unique Code

[John_V12]

Hi everyone,

I have a data set with multiple years in a column and I also have the size of the area that is repeating for each year.

I want to SUM the Area and then divide by the COUNT of Years per UniqueID.

Can anyone advise the best way to approach this.

Thank you,

[John_V12]

@goncalogeraldes I am looking for the count per UniqueID and not count of all the years in the dataset: E.g for the example I gave in the question should be SUM(table[Area]) and divide by COUNT(table[Year]) Filter by UniqueID.

Hope this makes sense

[goncalogeraldes]

@John_V12 Power BI calculates every measure on the go depending on the filtering context you provide on the visual. That being said, try using the measure I provided you and putting into a Matrix or Table where the rows values are the UniqueIDs. 

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

[John_V12]

@goncalogeraldes thank you very much.

[goncalogeraldes · Accepted solution]

Hello there @John_V12 ! Is this what you are looking for?

Sum / Count =
var _num = SUM('Table'[Area])
var _denom = COUNTA('Table'[Year]) /* or DISTINCTCOUNT('Table'[Year]) */

return
DIVIDE( _num, _denom, 0)

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes