Using calculate to filter table in measure

[arca123]

Hey!
I think I am having a brain fart, but I can't make my calculate measure work.

I am trying calculate an expression over a table, but I want to include only unique rows. So I use a calculate measure with a distinct or values filter. But this doesn't seem to work and I do not understand why.

Here is a sample table I have 

Event	Value
1	0
2	3
2	3

I am trying to calculate average of [Value], but to use only unique [Event] rows. So average here would be 1,5. As Event 1 Value = 0 and Event 2 Value = 3. Average = 0+3/2=1,5

So I wrote this measure. Average = 

CALCULATE(AVERAGE(SampleData[Value]),values(SampleData[Event]))

But it returns 2. Same if I substitute values with distinct. I'm not sure what's going wrong here. Any help, please?

[devesh_gupta · Accepted solution]

@arca123 You should modify your measure as:

Average = AVERAGEX(Summarize(SampleData,SampleData[Event],SampleData[Value]), [Value])

If it helps, please provide a kudo, and mark it as an accepted solution so that other users can find it more easily.

[arca123]

Hi @devesh_gupta thank you, this works! Can you please help me understand or guide me towards resources where your solution is explored in more depth? I do not really understand why my intended solution did not work and I do not understand why Summarize function was needed to be used?

[devesh_gupta]

@arca123 Actually summarize takes a table and certain GroupBy columns and returns a table of distinct values, so in the expression of Averagex I've taken summarize to get distinct combination from both the columns, then it will calculate the desired average.

This idea is taken from this tutorial at given timestamp: https://youtu.be/cN8AO3_vmlY?t=27340

Thank You.