Filter values per row and not in total column

[Mowteng]

I have a table that shows the numbers of products sold. I want to show the least sold numbers per year, for example less than 60 sales in 2019, 2020 and 2021.
When I use a filter in Power BI, the results are filtered into the column totals.
How can I filter the products at row level where this is broken down by year? I would like to do this by using a DAX measure. See the picture below for the example.

Translated with www.DeepL.com/Translator (free version)

[amitchandak]

@Mowteng , Not very clear

sumx(filter( Addcolumns ( Summarize( Table, Table[item], Table[Year]) , "_1", [You measure]), [_1] >60), [_1])

[Mowteng]

I am quite new to working with DAX, sorry for my explanation. . This is my current DAX expression:

Calculate (
    SUM(factSalesOrder[InvoicedQuantity]),
    FILTER(
        ALL(factSalesOrder[InvoicedQuantity]),
        factSalesOrder[InvoicedQuantity] <60)
)

I would like to compare the total sold quantities per year, but not more than 60 products. To show the least-sold numbers, in order to gain insight for purchasing management. 

[v-yiruan-msft · Accepted solution]

Hi @Mowteng ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Create two measures as below to get the sum of quantity

Measure = 
VAR _qty =
    CALCULATE (
        SUM ( 'factSalesOrder'[InvoicedQuantity] ),
        ALLEXCEPT ( 'factSalesOrder', 'factSalesOrder'[Year] )
    )
RETURN
   IF ( _qty >= 60, BLANK (), SUM ( 'factSalesOrder'[InvoicedQuantity] ) )

Measure 2 = SUMX ( VALUES ( 'factSalesOrder'[Year] ), [Measure] )

2. Create a matrix visual as below

If the above ones can't help you get the expected result, please share some sample data(exclude sensitive data) and your expected result with calculation logic and special examples. It is better if you can share a simplified pbix file and describe more details on your desired result. Thank you.

Best Regards

[Mowteng]

Thanks for your elaboration and the example, the DAX formula works!