cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
briguin
Helper I
Helper I

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

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.

 

briguin_1-1613856967094.png

 

 

 

Raw Table:

Prod_codeSalesQtyPeriod

Prod1-201
Prod11101
Prod11502
Prod2301
Prod2751
Prod21252

 

1 ACCEPTED SOLUTION
AlB
Super User III
Super User III

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] ) ) )
)

 

SU18_powerbi_badge

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.

 

View solution in original post

4 REPLIES 4
AlB
Super User III
Super User III

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] ) ) )
)

 

SU18_powerbi_badge

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.

 

View solution in original post

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
Super User III
Super User III

@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

 

SU18_powerbi_badge

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.

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors