Count of Products with > 100 units in Sales (Aggregated filter)

[briguin]

I run across this type of scenario all the time and have never figured it out.

In my source data, I have multiple entries for products sold during a single period.

I want to count the number of products that have a sum > 100 for each period (4 weeks)

I'm trying to do it in DAX within a single measure. Seems like I need to do an aggregate of the raw table and apply a calculation filter.

Raw Table:

Prod_codeSalesQtyPeriod

Prod1	-20	1
Prod1	110	1
Prod1	150	2
Prod2	30	1
Prod2	75	1
Prod2	125	2

[AlB · Accepted solution]

Hi @briguin 

Assuming you have period in a visual table as shown, place this measure in the table visual:

Measure ProdCt =
COUNTROWS (
    FILTER ( DISTINCT ( Table1[ProdCode] ), CALCULATE ( SUM ( Table1[Sales] ) ) )
)

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[briguin]

I made a slight adjustment and it worked

TEST_Sku_Count(CY) > 200 = COUNTROWS(
    FILTER(Distinct(SalesBySrceLoc_Pd[PROD_CODE]),
    CALCULATE(
        Sum(SalesBySrceLoc_Pd[SALES_AMT]),
        SalesBySrceLoc_Pd[SALES_AMT]>200
        )
    )
)

Can I expand the question: What if I needed to filter by > 200 and a Year = 2020. My brain conceptually blows up when I have to actual filter that inner calculate

I need something like 

TEST_Sku_Count(CY) > 200 = COUNTROWS(
    FILTER(Distinct(SalesBySrceLoc_Pd[PROD_CODE]),
    CALCULATE(
        Sum(SalesBySrceLoc_Pd[SALES_AMT]),
        SalesBySrceLoc_Pd[SALES_AMT]>200 && SalesBySrceLoc_Pd[YR]= 2020
        )
    )
)

[AlB]

@briguin 

TEST_Sku_Count(CY) > 200 V2 =
COUNTROWS (
    FILTER (
        DISTINCT ( SalesBySrceLoc_Pd[PROD_CODE] ),
        CALCULATE ( SUM ( SalesBySrceLoc_Pd[SALES_AMT] ), SalesBySrceLoc_Pd[YR] = 2020 ) > 200
    )
)

 It is advisable though to use a calendar table

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Ashish_Mathur]

Hi,

Create a Calendar Table and build a relationship from the Date column of your base data table to the Date column of your Calendar Table.  In the Calendar Table, extract the Year via a calculated column formula.  To your slicer, drag Year from the Calendar Table.  The measure suggested by AIB should work.