Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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. |
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. |
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
)
)
)
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. |
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.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |