Forum Discussion
Advocate IUsing calculate to filter table in measure
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 =
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.
3 Replies
devesh_guptaImpactful Individual
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.
arca123Hi 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.