weird behavior with filtering

[PowerrrBrrr]

Below are the screen shot of two tables in Power Bi. One without any filter and the other is filter with Average as 0 and what I get is count as 10 , while ideally it should be 1 as you can see in above table, there is only one row where average is zero.

Why this weird behaviour??

If I add ExternalId column in 2nd table I get count as 1 and when I removed it I get count as 10?? Can someone please explain me what wrong am i doing here??

Filter of 2nd Table

[MFelix · Accepted solution]

Hi @PowerrrBrrr ,

This is not a weird behaviour this is related with the context of the calculation.

When using a table to summarize the values, the more details you add the more specific the calculation gets so in this case when you look at the first table at a row level you get the average value for each of the lines and you have a line with 0. The same thing for the count of ID in a detailed view you get 1 for each row.

If you look at the total line of the first table you will see that the average is 1.850.630,69.

When you remove the other columns except for the average and the ID count the context is at the table level so the average value is the same (1.850.630,69) and the count of ID is also 10, even if you do the filter of the average that will not work because in this case the average is being calculated for the group values so you are comparing the 1.850.630,69 to 0 and not removing the 0 averages for details.

In this case you need to do a different calculation for the ID count adding  a measure that makes the row context the same at the level you need.

Try the following measure:

IDCOUNT =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            Table,
            Table[ExternalID],
            Table[TimeStamp],
            "AVERAGEVALUE", AVERAGE ( Table[Columntobeaveraged] )
        ),
        [AVERAGEVALUE] = 0
    )
)

This may need some adjustments