Distinctcount values by custom filter

Hi, polman4 

You can try the following methods.

Calculated Column:

Sum =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        'Table',
        [Product] = EARLIER ( 'Table'[Product] )
            && [Year] = EARLIER ( 'Table'[Year] )
    )
)

Revenue = SWITCH(TRUE(),
[Sum]>=0&&[Sum]<=10,"0 to 10",
[Sum]>10&&[Sum]<=20,"11 to 20",
[Sum]>20&&[Sum]<=30,"21 to 30")

Result:

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

polman4 ,

Since I don't have your new file, I will make a couple of suggestions:

1) Create a new Calculated Column:  Year = YEAR( Append1[Time] ).  This will make things easier to see as you continue to progress.

I believe you have the Filter in the wrong spot in your new Sites_per_year table.

Sites_per_year = 
SUMMARIZECOLUMNS(
        Append1[Year], 
        Append1[Name (Site)],
        "Inventory", sum(Append1[Total inventory]),
        FILTER (Append1, Append1[Year] = 2022))

Give this a shot and hopefully you can continue on your way.

Regards,